Excel dates

[ron]

I have regional settings set to English UK in COntrol panel
I have cells formatted to Englih UK date 14/01/2001
When i type in dates that are obviously English (e.g. 27/01/2004) all
is OK
If I type in a date that is ambiguous, say 04/05/2004 (4th May) it is
formatted to American and displays as 5th April

Any ideas anyone. I am pulling my hair out. This spreadsheet is used
by around 100 tutors submitted test data for importing to the
database, and the dates are crazy)

 

[Rob Schneider]

My Excel 2003 work as expected. Which version of Excel do you have?
Exactly how do you have short and long date defined in Regional and
Langauge Settings? Setting just English UK may not be enough.

I'm not sure what you are saying. You said you are formatted with UK
English, e.g. "14/01/2001", yet it displays "5th April". These are
different. Which is it?

Hope this is useful to you. Let us know.

rms

 

[ron]

Rob, I am using Excel XP. Control panelsettings are English,
DD/MM/YYYY, Excel cell format settings are als set to English,
DD/MM/YYYY. All is fine if I type in an unambiguous date such as
24/06/2004 (24th June), the English Format stays. If the date enterd
is ambiguous such as 11/05/2004 (meaning 11th May in English Format)
Excel, converts it to 5th Novemeber. Incidentally when I then cehck
the cell format, it has an asterix necxt to the format

 

[Bob Eyster]

Hi Rob,

Your two examples are correctly displayed. The only think you are doing is
telling Excel how you want the date displayed. Any day entered 1-12
translates it as months and not days. 13-31 will be translated as days.


Bob Eyster