I came across a line of code that formatted a date in VBA like this: Format(aDate,”short date”). And I didn’t get it. So with a little investigation, it turns out that “short date” (and “medium date” and “long date”) relate to the specified system methods of expressing dates found in the regional settings. So I wrote this little bit of code:
Sub testit()
Dim aDate As Date
aDate = Date
Debug.Print Format(aDate, "short date")
Debug.Print Format(aDate, "medium date")
Debug.Print Format(aDate, "long date")
End Sub
This is what I got:
06/05/2012
5-Jun-12
Tuesday, June 5, 2012
On both my XP box and my Mac, except that on the Mac, “long date” maps to Full. Trouble is, on neither machine do I have a “medium” setting, or a setting that maps to “medium date”. So. it looks like “medium date” is XL internal.
I’m not sure what great good these choices do. It would appear to be full proof to just specify the format. This capability does not extend to the spreadsheet TEXT() function.
Here’s a page that covers it: http://www.techonthenet.com/excel/formulas/format_date.php
You can also do the same with Time.
…mrt
©¿©¬
The “great good” that they do is they localize the date display. For example, Americans will write
Format$(Now, “dd/mm/yyyy”)
which could confuse Europeans. If you write
Format$(Now, “short date”)
then the date will automatically format according to the system date format.
Actually it should be
[cc lang=’vb’]Format$(Now, “mm/dd/yyyy”)
Michael: I haven’t tested this but I suspect that the use of short/medium/long tokens will change the result when the system settings change. So, for example, short date won’t always be mm/dd/yyyy but could be dd/mm/yyyy. That’s the key advantage over actually specifying the format.
I had hoped the techonthenet webpage would contain more details on the interaction between the system settings and the format code. However, it is essentially just an abbreviated version of what’s in the Excel VBA help file.
Finally, on the “medium” part, the help file also finesses the issue. {grin}
Long Date: Display a date according to your system’s long date format.
Medium Date: Display a date using the medium date format appropriate for the language version of the host application.
Short Date: Display a date using your system’s short date format.
I hard code my formats everywhere to dd\-mmm\-yyyy
Tushar –
I did test it, and it does what you expected: Change the regional formulation and XL follows along.
I think you and JP have found the value here that I missed.
… mrt
I just finished doing 24 hours of VBA programming on a report for a client in the Netherlands, where Dutch(Netherlands) is the region code they use. I ran into some problems, as the TEXT function doesn’t handle the “short date” argument and the Dutch language changes it slightly. We in the USA use m/d/yyyy and the Dutch short date format is d-M-jjjj, which threw me for a loop because the yyyy was replaced by jjjj.
I had some VBA code that used the Format function, but after reading your article I realized that I could have written a VBA Function using the Format function that takes a date and uses this “short date” argument, and substituted this for the TEXT function. It would have saved me some time.
Whenever I’m dealing with different Regional settings I also use the VBA Application.International(Index) property to help me out. VBA Help lists numerous Index constants that are a valuable resource when dealing with region settings.
All like the VBA fuction FormatDateTime
MsgBox FormatDateTime(Date, vbGeneralDate)
MsgBox FormatDateTime(Date, vbLongDate)
MsgBox FormatDateTime(Now, vbLongTime)
MsgBox FormatDateTime(Date, vbShortDate)
MsgBox FormatDateTime(Now, vbShortTime)