Date format changes when editing it
Solved/Closed
Rich
-
Nov 7, 2015 at 10:45 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 9, 2015 at 12:28 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Nov 9, 2015 at 12:28 PM
Related:
- Date format changes when editing it
- Excel date format dd.mm.yyyy - Guide
- Format factory - Download - Other
- Kingston format utility - Download - Storage
- Samsung format code - Guide
- Marksheet format in excel - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Nov 9, 2015 at 09:34 AM
Nov 9, 2015 at 09:34 AM
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
Nov 9, 2015 at 11:06 AM
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.
Nov 9, 2015 at 11:44 AM
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".
Nov 9, 2015 at 12:11 PM
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!
Nov 9, 2015 at 12:28 PM