CDate to recognise Australian date format?

Question

I have a spreadsheet I created mid-August which has a table with one column for every day since then. The column header is the day's date. The date is displayed how I want it to be displayed: "dd/mm/yyyy"

However, I have macro which needs to read these dates in order to perform its work. Because I am using a table, the dates seem to be held as text rather than as 5-digit numbers (when I set the header cells' format to "General", it remains with the "dd/mm/yyyy" format rather than changing to a 5-digit number. This wasn't a problem until the 1st of September, after which CDate (in the macro) began reading these dates as "mm/dd/yyyy"

Is there any way to make CDate recognise the Australian date format rather than the American? Or failing that, make the table headers stay as dates rather than as text? I'd rather avoid having to convert the Table back to a range, as the macro is designed to use table attributes, ListColumns etc.

UPDATE:

I've narrowed down the problem. It isn't the date, but the ListColumn.Name property. For some reason it is re-converting my date. I'm using Format() to set the date format, like so:

CreateHistoryColumn.Name = Format(headerDate, "dd/mm/yyyy")

MsgBox Day(headerDate) & "/" & Month(headerDate) & "/" & Year(headerDate)
MsgBox Format(headerDate, "dd/mm/yyyy")
MsgBox CreateHistoryColumn.Name

Where CreateHistoryCol is an object of class ListColumn. The 3 MsgBox calls display the following:

6/9/2012 06/09/2012 9/06/2012

So the Format() call formats the date correctly, but the call to ListColumn.Name serves to alter the format to "mm/dd/yyyy".

Thanks jozzas. I looked at that one but I was hoping there might be a more direct way. Bloody vba :/ — Swiftslide

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2012-09-05T08:09:41

From help file:

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings.

I hate relying on the locale because most of the time it seems to do the opposite of what I intended, plus it makes code less portable.

I usually deal with dates as explicitly as possible:

Split your string into the various parts (year, month, day, as well as hour, minute, second if relevant)
Stick the various parts in the DateSerial function. This returns a Date type. (To that you can add the output of TimeSerial, if applicable.)
If necessary for display purposes, format this Date as appropriate using the Format function, which returns a string.

Here's a worked example.

CDate to recognise Australian date format?

2 Answers