Excel Dates (XP (but others also?))

Topic

New Reply

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)

Ron

Reply | Quote

Replies

Can you check Control Panel > Regional Settings to see what date format is selected there.

StuartR

Reply | Quote

Can you check Control Panel > Regional Settings to see what date format is selected there.

StuartR

Reply | Quote

Excel uses the date format set in the Regional Options / Regional and Language Options control panel, except for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.

Reply | Quote

As stated in the original post, the control panel settings are English, as are the cell formats in Excel, and all is fine unless the date is ambiguous, when it switches to American

Reply | Quote

I have just noticed that when I check the cell formatting after Excel has converted, the date the format type has an asterix (*) next to it. Not sure what this means. So, all formats everywhere set to English dd/mm/yyyy, I type in 11/05/2004 (meaning 11th May) Excel immediatley converts it to American (5th November), on checking the cell format it as *03/14/2001

Reply | Quote

I have xl 2000 and don’t have this problem, under these circumstances.
I do have the problem if I use a macro to import a load of dates from a csv file from elsewhere (but not if the copy and paste is done by hand.)
A thread on this topic a few weeks ago suggested that vba is exclusively US English as far as dates go.
As a shot in the dark, I suggest you do a custom format of the cells as ddd d mmm yyyy so that the date entered as, say, 3/6/5 shows as Fri 3 Jun 2005 – OR NOT!
At least the error, if there is one, is then more obvious.
You’ve probably noticed that if the date is in the current year, then merely entering it as 3/6 is enough (it displays as Thu 3 Jun 2004.)
The way the date is displayed is immaterial to any arithmetic done on the date. It’s still a date number, really.
Best of luck!

Reply | Quote

I will try the custom idea. The problem is that although it does not matter to much inExcel, the dates are imported into a much larger database that keeps track of student test dates and scores. The variance in date types is maintained, which therfore means test dates are inaccuratley portrayed.

Reply | Quote

Ron,
I’m guessing you are using Win XP too – if not you may have to vary instructions slightly. In the Control Panel, next to the dropdown that says settings are English (United Kingdom), there should be a Customize button. If you click on that and then check the date tab, does the short date format show as dd/MM/yyyy or MM/dd/yyyy? It is possible to customize this but the dropdown list will still show it as English (United Kingdom).
Might be worth checking.

Reply | Quote

Ron,
I’m guessing you are using Win XP too – if not you may have to vary instructions slightly. In the Control Panel, next to the dropdown that says settings are English (United Kingdom), there should be a Customize button. If you click on that and then check the date tab, does the short date format show as dd/MM/yyyy or MM/dd/yyyy? It is possible to customize this but the dropdown list will still show it as English (United Kingdom).
Might be worth checking.

Reply | Quote

I will try the custom idea. The problem is that although it does not matter to much inExcel, the dates are imported into a much larger database that keeps track of student test dates and scores. The variance in date types is maintained, which therfore means test dates are inaccuratley portrayed.

Reply | Quote

I have xl 2000 and don’t have this problem, under these circumstances.
I do have the problem if I use a macro to import a load of dates from a csv file from elsewhere (but not if the copy and paste is done by hand.)
A thread on this topic a few weeks ago suggested that vba is exclusively US English as far as dates go.
As a shot in the dark, I suggest you do a custom format of the cells as ddd d mmm yyyy so that the date entered as, say, 3/6/5 shows as Fri 3 Jun 2005 – OR NOT!
At least the error, if there is one, is then more obvious.
You’ve probably noticed that if the date is in the current year, then merely entering it as 3/6 is enough (it displays as Thu 3 Jun 2004.)
The way the date is displayed is immaterial to any arithmetic done on the date. It’s still a date number, really.
Best of luck!

Reply | Quote

Although I have no reason to doubt your statements, the format “*03/14/2001” suggests strongly that there must be a US setting somewhere in your system. The * before the format indicates that the format follows the date setting of the operating system.

You might check the keyboard input language; that’s what determines the default language in Word, perhaps it plays a role in Excel too (it doesn’t influence the date setting on my system, but it takes only a minute to check.) Under Windows XP, the keyboard input language is set in the Languages tab of the Regional Settings control panel; in older Windows versions, it is in the Keyboard control panel.

Reply | Quote

Flying blind, here, Ron, but if Hans’ solution doesn’t do the trick, but if the custom format trick works at least in Excel, is there any way of exporting the dates as text, and converting them back to real dates in the database application?

Reply | Quote

Flying blind, here, Ron, but if Hans’ solution doesn’t do the trick, but if the custom format trick works at least in Excel, is there any way of exporting the dates as text, and converting them back to real dates in the database application?

Reply | Quote

Although I have no reason to doubt your statements, the format “*03/14/2001” suggests strongly that there must be a US setting somewhere in your system. The * before the format indicates that the format follows the date setting of the operating system.

You might check the keyboard input language; that’s what determines the default language in Word, perhaps it plays a role in Excel too (it doesn’t influence the date setting on my system, but it takes only a minute to check.) Under Windows XP, the keyboard input language is set in the Languages tab of the Regional Settings control panel; in older Windows versions, it is in the Keyboard control panel.

Reply | Quote

I have just noticed that when I check the cell formatting after Excel has converted, the date the format type has an asterix (*) next to it. Not sure what this means. So, all formats everywhere set to English dd/mm/yyyy, I type in 11/05/2004 (meaning 11th May) Excel immediatley converts it to American (5th November), on checking the cell format it as *03/14/2001

Reply | Quote

As stated in the original post, the control panel settings are English, as are the cell formats in Excel, and all is fine unless the date is ambiguous, when it switches to American

Reply | Quote

Excel uses the date format set in the Regional Options / Regional and Language Options control panel, except for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.

Reply | Quote

A few questions.
1) When you type 27/01/2004 into an English-US cell, you would get 27/01/2004 even when the format is set to date dd/mm/yyyy. Excel would not be able to convert it and thus would leave it as-is which would appear to be English-UK correct. Maybe those entries that you believe are working correctly are not really formatted in English-UK and are just being read as text? If you try entering 27-01-2004 does it convert to 27/01/2004 or does it keep the dashes? I’m just trying to decipher whether the file is being read/formatted in both English/UK and English/US. If you would find that only English/US is really being used it would narrow down your search for the problem.

2) Does this only occur on this particular spreadsheet or have you noticed problems on other files you run on your system?

3) Did you create this spreadsheet or did whoever made it place some coding that you are unaware of into it which adjusts formatting for some reason?

yoyoPHIL

Reply | Quote

By jove, I think he’s got i ;-))
I have experimented at length and deduced that you cannot type in an English date, just format to look like an Englsh date. So when I type in 28/12/2004, meaning 28th December, even though the cell is formatted to English UK, EXCEL still expects the 28 to be a month, which of course it cannot be ‘cos there are only 12, it therefore treats the whole cell as text !!!!
Thanks Bill !
So us English / English folk cannot really use English, unless we know how to play along to Bill’s little game.
Incdentally if I type in 28 dec, it works fine.
Now all I need to do is determine how to best sort this all out

Thanks for all your help, much appreciated

Reply | Quote

There MUST be something wrong with your settings. It IS possible to enter dates in UK date format. However, if you have checked all the suggestions made in this thread, I don’t know what else to try.

Reply | Quote

There MUST be something wrong with your settings. It IS possible to enter dates in UK date format. However, if you have checked all the suggestions made in this thread, I don’t know what else to try.

Reply | Quote

Its PHIL not Bill but at least you didn’t call me Woody (Hans has been mislabeled Woody already).

What I get from your answer to my first question was that your spreadsheet is acting as though it is set to English/US and not English/UK as you think. As Hans stated, “Excel uses the date format set in the Regional Options / Regional and Language Options control panel, EXCEPT for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.”
If your control panel setting is set to English/UK then I believe you have a setting somewhere which tells the file/sheet to use English/US.
Did you try opening a new file and entering dates and did it act the same way? If the new file acted correctly, that is, the date was properly configured in English/UK, then the problem is within your original file and not the system settings. Hopefully this gets you on the right track to finding the problem.

yoyoPHIL

Reply | Quote

Had a similar problem down under. Despite settings of English (Australian), I was getting US Date formats. I used a sledge hammer and created a custom date format. Saved heaps of time mucking around and a few inches on my hairline

Reply | Quote

Had a similar problem down under. Despite settings of English (Australian), I was getting US Date formats. I used a sledge hammer and created a custom date format. Saved heaps of time mucking around and a few inches on my hairline

Reply | Quote

Its PHIL not Bill but at least you didn’t call me Woody (Hans has been mislabeled Woody already).

What I get from your answer to my first question was that your spreadsheet is acting as though it is set to English/US and not English/UK as you think. As Hans stated, “Excel uses the date format set in the Regional Options / Regional and Language Options control panel, EXCEPT for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.”
If your control panel setting is set to English/UK then I believe you have a setting somewhere which tells the file/sheet to use English/US.
Did you try opening a new file and entering dates and did it act the same way? If the new file acted correctly, that is, the date was properly configured in English/UK, then the problem is within your original file and not the system settings. Hopefully this gets you on the right track to finding the problem.

yoyoPHIL

Reply | Quote

By jove, I think he’s got i ;-))
I have experimented at length and deduced that you cannot type in an English date, just format to look like an Englsh date. So when I type in 28/12/2004, meaning 28th December, even though the cell is formatted to English UK, EXCEL still expects the 28 to be a month, which of course it cannot be ‘cos there are only 12, it therefore treats the whole cell as text !!!!
Thanks Bill !
So us English / English folk cannot really use English, unless we know how to play along to Bill’s little game.
Incdentally if I type in 28 dec, it works fine.
Now all I need to do is determine how to best sort this all out

Thanks for all your help, much appreciated

Reply | Quote