Date Literals

When coding in VBA, you can write dates using a format of #01/01/2007#, which is obviously January 1, 2007. But what is #02/01/2007#? Is it January 2 or February 1? It's actually February 1, 2007. This is because when coding in Excel, you do so in American English, regardless of any other settings you may have, and hence you must use U.S.-formatted date literals (mm/dd/yyyy format). If other formats are typed in (such as #yyyy-mm-dd#), Excel will convert them to the #mm/dd/yyyy# order.

What happens if you happen to be Norwegian or British and try typing in your local date format (which you will do at some time, usually near a deadline)? If you type in a Norwegian-formatted date literal, #02.01.2007#, you get a syntax error, which at least alerts you to the mistake you made. However, if you type in dates in a UK format (dd/mm/yyyy format) things get a little more interesting. VBA recognizes the date and so doesn't give an error, but "sees" that you have the day and month the wrong way around; it swaps them for you. So, typing in dates from January 10, 2007 to January 15, 2007 results in:

You Typed

VBA Shows

Meaning

10/1/2007

10/1/2007

October 1, 2007

11/1/2007

11/1/2007

November 1, 2007

12/1/2007

12/1/2007

December 1, 2007

13/1/2007

1/13/2007

January 13, 2007

14/1/2007

1/14/2007

January 14, 2007

15/1/2007

1/15/2007

January 15, 2007

If these literals are sprinkled through your code, you will not notice the errors.

It is much safer to avoid using date literals and use the VBA functions DateSerial(Year, Month, Day) or DateValue(DateString), where DateString is a non-ambiguous string such as "January 1, 2007". Both of these functions return the corresponding Date number.

0 0

Post a comment