Date mess-up on CSV output in VBA?

[ErikJan]

I always use dates/times as dd/mmm/yyyy hh:mm:ss

In VBA, I export sheets to CSV. It looks as if some of my dates are getting garbled (days & months swapped). Again, I see this in the exported CSV when I look at it via e.g. Notepad.... I haven't been able to get to the root-cause but was wondering (A) if this is known [which prevents me from re-inventing wheels] and (B) what can be done to avoid this..??

Thanks!

[Rudi]

In Excel the dd/mmm/yyyy is a format that is assigned to dates. You see it in this format in Excel, but it does not mean that the PC sees it in this format. Your regional setting may be yyyy-mm-dd, and if this is the case, the export may send it to the text file based on the original date format, and not the format of the cells in Excel.

Either change the date format on your system, or you will need to construct a format in VBA to remodel the date as it sends it to the text file.

[HansV]

VBA inherently uses US English formats: the point is used as decimal separator, and the date format is m/d/yyyy.

If you format the dates as dd-mmm-yyyy, they should be OK, but if you use dd/mm/yyyy or similar, confusion arises.
You could also use formulas of the form =TEXT(A1,"dd-mmm-yyyy")

The real solution is obviously to become an American.

[ErikJan]

Somehow my "Quick reply" got lost twice...

My setting in Windows is identical to that used in Excel.

Could I convert to text by adding a leading single quote and write out the month like: '06-Apr-2010 ??

Could I assume that upon CSV IMPORT and Excel would convert to a proper date matching that of the local settings in Excel and the OS?

[HansV]

Inserting an apostrophe before the date converts it to text. It should export OK, but the apostrophe won't be saved with the .csv file, so next time you open the .csv file in Excel, the apostrophe will be lost.

It's all very tricky. If I have to work with CSV files, I usually set my Windows locale to US temporarily.

[HansV]

BTW, if you're composing a Quick Reply, you must click Submit, not POSTREPLY - the latter button will destroy your quick reply.