Date Formatting - American Style

[thunderfoot]

Using EXCEL 2003 and although cell B2 is formatted to dd/mm/yy format, and I have checked the Region settings in Control Panel, the date is being dispalyed as mm/dd/yy format. Any ideas???

[osknows]

Are you typing the date into the cell or importing external data?

[osknows]

I just realized your talking about GRUSS itself and a freshly installed Excel 2003?

If any of the following doesn't work then I'm unsure...

In Microsoft Windows, you can change the way two-digit years are interpreted for all Microsoft Windows programs that you have installed.

Click the Start button, and then click Control Panel.

Do one of the following:
In Windows Vista, click Clock, Language, and Region.
In Windows XP, click Date, Time, Language, and Regional Options.
Click Regional and Language Options.
Do one of the following:
In Windows Vista, in the Regional and Language Options dialog box, click the Formats tab.
In Windows XP, in the Regional and Language Options dialog box, click the Regional Options tab.
Do one of the following:
In Windows Vista, click Customize this format.
In Windows XP, click Customize.
Click the Date tab.
In the When a two-digit year is entered, interpret it as a year between box, change the upper limit for the century.
As you change the upper-limit year, the lower-limit year automatically changes.

[osknows]

I've just realised that my dates are reversed as well??

[osknows]

Excel can be problematic with dates and this is the strangest I've seen yet

If excel cell B2 is formatted as text all is ok.

If excel cell B2 is formatted as date or custom dd/mm/yyyy then all is okay only if the date is not interchangeable; eg dd is 13 or more.

If dd is 12 or less my date reverses and becomes mm/dd/yyyy; so today becomes 11th June 09.

I've never noticed this before as I don't really use the date field but there are ways to overcome this using excel formula. Would be interesting to hear others experiences first though.

Also, Gary/Mark testing this in excel 2007 I found .xlsx and .xlsm files aren't recognised...is that right?

[thunderfoot]

......... and a freshly installed Excel 2003?

How did you guess? ......... and a freshly installed Excel 2007

[osknows]

Call it intuition...