How to stop ListColumn.Name changing the format of my dates?

[Swiftslide]

I have an excel table whose column headers are dates, going back to September 2011. There is one date for each day (I'm up to column MY!).

In mid-August I added a Macro that would generate new columns automatically and add data from another sheet. It worked fine until the start of this month. Because the dates so far this month can be interpreted validly in either Australian ("dd/mm/yyyy") or American ("mm/dd/yyyy") formats, the dates added were now in the latter, where previous dates were in the former.

I narrowed down the issue to a call to ListColumn.Name. The code below illustrates the issue:

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

Where CreateHistoryColumn is an object of class ListColumn. The three calls to MsgBox display the following:

6/9/2012
06/09/2012
9/06/20122​

So the call to the .Name property is actually re-formatting the dates to American format (and because there is already a "9/06/2012" in the table, from June 9th, it adds a "2").

I tried removing the call to format, passing the .Name property the headerDate directly, but this did not fix the issue. Can anyone help?

 

[AlphaFrog]

This is a bit of a guess but give this a try...

CreateHistoryColumn.Name = CLng(headerDate)

 

[Swiftslide]

Thanks AlphaFrog, works a treat.