Excel screws up my dates when system is set to Eur

Question

Here's what I'm doing: 1. In Control Panel I set up the system date format as follows: dd/MM/yyyy 2. I create the following simple macro:

Sub hello()
    ActiveSheet.Cells(1, 1) = "10/01/2014"
End Sub

1. I run it, and get the following date in the cell (1,1): 01/10/2014

Whenever the date is less then 12 it converts the date to american format (MM/dd/yyyy), and when the date is more than 12 it fails to convert it and writes the date in the cell correctly, but as text. I first discovered this when trying to write dates from VSTO, but then narrowed it down to any place where you write a date to a cell from code, even Macros. Is that an Excel bug or am I doing something wrong? I'm using Excel 2013, but I believe this problem exists in all other versions as well...

Answer 1

VBA considers all date literals to be in US format unless the literal cannot possibly be a valid US-formatted date. If you use "10/Jan/2014" (or #10/Jan/2014#; the #s are date literal markers), VBA will fall back to dd/MMM/yyyy since it can't be anything else.

If you need VBA to understand your local date format, use:

Sub hello()
    ActiveSheet.Cells(1, 1) = DateValue("10/01/2014")
End Sub

...since (as stated in the VBA Help files):

DateValue recognizes the order for month, day, and year according to the Short Date format you specified for your system. DateValue also recognizes unambiguous dates that contain month names, either in long or abbreviated form.

So, as long as your PC has the correct locale set, DateValue() should understand your preferred date format.