Dates in VBA ???

[erik.van.geit]

Hi, gurus,

running this

Sub test()
MsgBox Date
ReportDate = Application.InputBox("Please enter report date", "REPORT DATE", Date)
'click OK
Range("A1") = ReportDate
Set c = Range("A1").Find(ReportDate)
If Not c Is Nothing Then MsgBox c
End Sub

I get
1. 5/07/05 ("European" format)
2. 7/5/2005 ("American" format)
NO third box

It's a very obvious formatproblem

what to do ?
can somebody point me to a good webpage or thread on this subject?

kind regards,
Erik

EDIT: just added a "Not" at the end of the code

 

[erik.van.geit]

Oorang,
thank you for follow up !

my datesettings in control pannel are (and were already)
short: d/MM/jj
long: dddd d MMMM jjjj
(j = y = year)
but - as said before - the inputbox puts the mm before the dd
or did I miss something ?

nevermind :->

Andrew,
thanks also to you for persisting here
Oh, YES, DateValue is the most sure method: I didn't succeed to trigger errors yet
Here is the code with an extra check to be sure the user does really know (or "agree with his machine) what he will try to find.

Option Explicit

Sub find_date()
    Dim DateToFind As String
    Dim DateFormat As String
    Dim response As String
    Dim msg As String
    Dim c As Range
    
    If IsDate(ActiveCell) Then DateToFind = ActiveCell Else DateToFind = Date
    DateFormat = "dd/mm/yy"
    
    Do
    DateToFind = InputBox("Enter date in format " & DateFormat, "Date", Format(DateToFind, DateFormat))
    
        If DateToFind = "" Or Not IsDate(DateToFind) Then
        MsgBox "operation canceled", 48, "END"
        Exit Sub
        End If
        
    msg = "The date to find is: " & Format(DateToFind, "dd mmmm yyyy") & Chr(10) & _
    "Is this correct?" & Chr(10) & Chr(10) & _
    "NOTE: whatever format you used in your workbook, the date will be found," & Chr(10) & _
    "as long it is not formatted as text."
    response = MsgBox(msg, 36, "CHECK DATE")
    
    Loop While response = vbNo
    
    Set c = Cells.Find(DateValue(DateToFind))
    If Not c Is Nothing Then
        c.Select
        MsgBox Chr(34) & DateToFind & Chr(34) & " found in cell " & c.Address(False, False), 64, "OK"
    Else
        MsgBox "No match found for " & Chr(34) & DateToFind & Chr(34), 48, "nothing"
    End If
End Sub

kind regards,
Erik[/code]

 

[MarkBoy]

Not that this is of much help, you seem to have worked this out already, but I can confirm that the Regional Settings in Control Panel determine the default way that Excel handles dates, but NOT the default way that VBA handles dates. Being from New Zealand, we also use dd/mm/yyyy, and I have struggled with this, until eventually I have found work-arounds. This best work around I have come across if it is today's date you are wanting entered, is instead of using the VBA today command, which gives the date in mm/dd/yyyy, to instead have today's date in a cell on the spreadsheet using the =TODAY() formula, then reading that into and out of VBA. VBA is still confused as to what the date actually is, but because it reads it in and out in the same format, the result is correct... This of course does not work if you need to manipulate the date within VBA (adding a day will add a month instead), so it is a lame workaround, but it was enought o accomplish what I needed for now.

I have to agree, I love Excel, and think it is an exceptionally powerful, and globally underutilised piece of software, but this oversight is exceptionally glaring, and I feel demonstrates the often introspective nature of Redmond...

 

[erik.van.geit]

thak you, MarkBoy, for you input

so it is a lame workaround

Indeed I've often seen workarounds for the problem. Isn't "DateValue" a solution for you too ?

kind regards,
Erik

 

[Andrew Poulsom]

The way it is interpreted is determined by your Control Panel settings.

How can this be retrieved? That would be intresting? I think it could help, but still not sure.

You can use:

MsgBox Application.International(xlDateOrder)
MsgBox Application.International(xlMDY)

xlDateOrder returns the order of date elements:
0 = month-day-year
1 = day-month-year
2 = year-month-day

xlMDY returns True if the date order is month-day-year for dates displayed in the long form; False if the date order is day-month-year.

 

[erik.van.geit]

Fine!
Thank you, Andrew!
you invited me with this to go to the help files
just didn't know what to search for
now it's clear: "International"


Erik

 

[Oorang]

Thanks Andrew great tip! I didn't know about that one

So really if you want code that is smart enough to figure out how to handle any date type you can use application.international to see how input is going to be read in the worksheet, something like "g = Range("A1").NumberFormat" to see how the date is then being formatted and then you can have the program make all it's decisioning based off of that.

 

[Andrew Poulsom]

Thanks Andrew great tip! I didn't know about that one

So really if you want code that is smart enough to figure out how to handle any date type you can use application.international to see how input is going to be read in the worksheet, something like "g = Range("A1").NumberFormat" to see how the date is then being formatted and then you can have the program make all it's decisioning based off of that.

As I said before the NumberFormat of a cell doesn't change what's in it, only how it is displayed. The DateValue function behaves like Excel does when you enter a date in a cell. The interpretation is determined by the user's Contol Panel settings.

You could use xlDateOrder to tell the user how the date should be formatted in the InputBox, like this:

Sub Test()
    Dim DateSep As String
    Dim DateFormat As String
    Dim DateString As String
    DateSep = Application.International(xlDateSeparator)
    Select Case Application.International(xlDateOrder)
        Case 0: DateFormat = "mm" & DateSep & "dd" & DateSep & "yy"
        Case 1: DateFormat = "dd" & DateSep & "mm" & DateSep & "yy"
        Case 2: DateFormat = "yy" & DateSep & "mm" & DateSep & "dd"
    End Select
    DateString = InputBox("Enter date in format " & DateFormat, "Date", Format(Date, DateFormat))
    If DateString = "" Or Not IsDate(DateString) Then
        MsgBox "operation canceled", 48, "END"
        Exit Sub
    End If
    MsgBox "Date is " & Format(DateValue(DateString), "dd mmmm yyyy")
End Sub

 

[Tom Urtis]

Just looking in on this thread, let me ask you guys a favor but first a few comments. The issue of varying date formats has been a thorn in many developers' sides when applications we design are used internationally by people who have their local visual interpretation of what a date value really is.

To re-state the obvious, formatting does not change the underlying value of a cell, only how the value appears. Whenever numbers are used in formats to represent dates, today's date for example, people in the UK will interpret July 11, 2005 as 11/07/05 whereas here in the United States we will interpret it as 07/11/05.

That issue will never go away. We are forever stuck with "international default thought". Requesting a date selection from a user by invoking numbers in the interface is begging for trouble, such as throwing an InputBox at them with the prompt "Enter date in MM/DD/YY format".

One cure I employ in my applications is to attack the date selection at the source, so that when my app calls for a user to select a date, the user is given 3 separate date fields that list the year in YYYY format, month in text format (MMMM), then the day from only a list that makes sense based on the combination of month and year (leap years for February, 31 days for March, 30 days for April, 31 etc).

This is not news to anyone, as a userform is the better interface with comboboxes to individually list the date serial components for the user to fully be aware of what date they are selecting.

The challenge after that becomes how to confirm for all subsequent users what the date truly is that they are actually referencing. It can never be left to unspoken user assumption. If the date I asked one user to specify is going to be used by someone else to reference as a Find criteria for example, then I prompt the user with a message box in the Find portion of the code with something like "For the date you are looking to Find, keep in mind, the date of reference is July 11, 2005.", using the long date format instead of only providing a cell value formatted locally.

Reference to date values formatted as local numbers never works. Again, nothing new, but many readers of these posts and people new to programming do not know how to build or program controls for a userform, and they need to provide a bulletproof date selection interface for their users by the proverbial yesterday. What might help new developers is a macro that temporarily creates a userform for date selection, with validation controls to make sure their users select the date serial components in order, and completely. Then, the selected date is written to a cell, to be used in future prompts for confirmation as needed per above.

I am building an elaborate Excel website that will be launched in 2006. In the mean time I have a temporary cookie-cutter one-page site to represent my "under construction" status. I plan to have a bunch of what I think will be useful developer examples. Coincidentally over this past weekend, I placed a date selector example on my site that you can take a look at and download. It sort of relates to this issue of Erik's, but generally I would be interested in anyone's opinion of my recommended date selection interface approach. I am not interested in comments about my site. The site is ugly and meant to be temporary. Comments about the actual approach and logic would be welcome, after you download the file and read my explanation in it.

Please email me your comments, we don't need to clog any threads here. As people can probably tell by the tone in my posts, I have a fairly thick skin so don't hold back on the negative comments if you have any. If you see aspects of the download and the logic that you like, tell me that too since it is important to know what works and makes sense.

A long-winded response here, I know, but this date format interpretation thing has given me headaches too so I took a crack at addressing it and maybe my idea can help others.

Click the WWW button below my post here and scroll down to the "Sample code: DateSelector" heading. A link to download the example is at the bottom of the page.

Thanks for any emailed comments and thanks to Erik for posting the issue.

 

[erik.van.geit]

OK, Tom,
I'll go there and mail my comments!

You're talking about bulletproof-code.
That's why this check was in one of my posts here.

    Do
    msg = "The date to find is: " & Format(DateToFind, "dd mmmm yyyy") & Chr(10) & _ 
    "Is this correct?" & Chr(10) & Chr(10) & _ 
    "NOTE: whatever format you used in your workbook, the date will be found," & Chr(10) & _ 
    "as long it is not formatted as text." 
    response = MsgBox(msg, 36, "CHECK DATE") 
    Loop While response = vbNo

Of course this still allows to first put in an error and the user wil then need to go back. A method of doing things right from first input is always preferable. But for "easy and quick coding" "board-style-made" is't rather useful

kind regards,
Erik