Date format changes when editing it [Solved/Closed]

Rich - Nov 7, 2015 at 10:45 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Nov 9, 2015 at 12:28 PM
Hi,

I'm using Excel 2007. I've changed the date format to dd/mm/yyyy with no problems.

However, as soon as I begin to edit a date, it changes to the format to mm/dd/yyyy, in both the cell being edited and formula bar. After I have made my changes and leave the cell, it reverts back to dd/mm/yyyy.

As you can imagine, this gets very confusing.

This has only started since I upgraded to Windows 10. I didn't have this issue using Windows 7.

Can anyone suggest a fix, please?

Thanks
See more 

5 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 9, 2015 at 09:34 AM
-1
Thank you
I thought that's how it is supposed to work. what you see when editing should be reflecting the system date format as defined for the windows. Formatting is just to show visually how it looks to the users
Thanks for the input, but to be honest, I don't follow your logic that this is what is supposed to happen.

Firstly, as I said originally, until I recently updated to Windows 10, the format during editing was the same as the format displayed afterwards. In other words, Excel didn't use to do what it's doing now, which leads me to conclude there's a setting issue somewhere.

Secondly, imagine you want view dates as dd/mm/yyyy (as is common for most counties outside the United States) but have to edit/input them as mm/dd/yyyy. The potential for confusion and mistakes is huge!

For example, you want to input 5th November, 2015... You automatically type it in the same order it will be displayed in your spreadsheet, the order you want: 05/11/2015. However, because the editing and viewing formats are different, when you've finished editing and leave that cell, Excel flips it around to 11/05/2015. So instead of inputing 5th November, you've actually input 11th May.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > Rich - Nov 9, 2015 at 11:44 AM
One thing you have to keep in mind is in excel, dates are not stored as mm/dd/yyyy or d/mm/yyyy format bur rather as numbers.

Having said that, what I was trying to say was for windows there is global setting of time, currency, language etc. This you can see in the control panel. So for example in some part of the world decimal is denoted by "." (dot) and in some it is by comma (,). So depending on what is configured, and what is meant by "," excel formatting instruction will behave differently.

My feeling is that before windows 10, the date was configured to British (dd/mm/yyyy). Now system is using US date format of "mm/dd/yyyy".
Rich > rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 9, 2015 at 12:11 PM
Sorry I misunderstood your point, rizvisa1.

You were absolutely right. Although I'm using English (UK) as my language in Windows, because I live in Canada I have to have my keyboard set to English (US) and my region to Canada, Eastern Time Zone. I think all these variations were confusing Windows. I have now set the default date format in Windows to dd/mm/yyyy (which surprisingly isn't a standard option and had to be user-defined, despite how commonly it's used worldwide).

In short, my Excel problem is now solved... thanks very much!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen > Rich - Nov 9, 2015 at 12:28 PM
Glad to be able to help here.