Thread: Excel date problem

hi guys, first post.

I`m having a problem with Excel date formatting which is really quite bizarre.

All of a sudden, on random dates, it has started to switch the month with the day, so if I enter 10/06/2007 it will switch it to 06/10/2007. This does not happen on every instance of this particular date or even on every date you enter, just seemingly random ones. It will also not switch it if the day that you enter is greater than 12, so 15/06/2007 will be left alone.

This has just started happening and I can't figure out why. One of my colleagues says she has encountered this problem before and resolved it by copying and pasting all of the data into a fresh Excel workbook and saving, which did work on the day I did it but it is continuing to do this.

I have never encountered this problem before but I have not previously been a heavy user of any MS Office products.

The cell is formatted as a date of type *14/03/2001. I have tried it as just 14/03/2001 but it doesn't make any difference. Apparently, the * means that it will not switch date orders with the operating system. The OS is set to UK dates.

I`d appreciate any help as I have just started my job and am trying to impress by reformatting all their Excel logs to be a bit more readable, this one is making me look a bit of a twit!

sorry for the double post but there doesn't appear to be an edit button on this forum.

One other thing that i`ve just realised is happening, not sure if this will help with the diagnosis, is that when you enter a date that Excel switches around (as mentioned above), if you enter it the other way, it switches it back!
So if I enter 02/10/2006 it switches it to 10/02/2006 but if I enter 10/02/2006 it switches it to 02/10/2006.

This just gets weirder by the minute!

Hello hermiod,

When you say "the OS is set to UK dates" do you mean the Regional settings?

What you describe is consistent with the worksheet cells being set with one format , i.e. dd/mm/yy and the regional settings set to the opposite, i.e. mm/dd/yy.

If that were the case then a date like 02/10/2007 would be interpreted as whatever the regional settings dictate, i.e. 10th Feb and would display as that, given your dd/mm/yy worksheet settings. I suggest you check regional settings in the control panel and change to dd/mm/yy if necessary. At the same time make sure that all your worksheet cells are also set to dd/mm/yy

When you input a date like 14/02/2007, if regional settings are set to mm/dd/yy this won't be interpreted as a date at all (because month is greater than 12) - excel just treats it as text - you can see this if you try to reformat it - you won't be able to because it hasn't been recognised as a number

thanks for all the replies guys.

I don't have access to the regional settings personally (the computers are on a company network and locked down to the hilt) but i`ve been assured by IT Services that the regional settings are correct. Not sure if I believe them though.

The solution I have found is to set the cell formatting to text, use a macro and format the entered date to a Short Date that way. i`ve tried this with the dates I was having problems with and it seems to be ok now.

Only problem with this method is that when you take off the date formatting, Excel turns all of the problem dates into serial dates. I have had to make another function to copy all of the column contents into another text-formatted column, then manually delete all the data in that column, set it to text format, then copy the copied data over.

So crisis has been averted but thanks all for the replies.