Dear Excel People,
How do I stop Excel from automatically changing my day to month and vice versa? I want the standard to be dd.mm, and that's what I chose on my cell formatting, but if I type 01/09, meaning first of September, it will automatically read ninth of January. If I type 13/09, it leaves it as is, thirteenth of September. So annoying
Thanks!
Check your regional settings (on your PC), they are probably set to Anglo Saxon dates
Check that your date and time settings in Windows are NOT set to US settings.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are no Anglo Saxon settings!They are probably set to Anglo Saxon dates
UK English settings are dd/mm/yyy.
US English settings are: m/d/yyyy
Last edited by AliGW; 09-29-2021 at 08:23 AM.
My settings are in Norwegian, but that seems to be the same as the UK, and not US:
Untitled.png
OK - I think you need to attach a small workbook that displays the issue for us to test.
Are you receiving the problematic data from a US source?
Let's see if I managed to attach it.
Ohh it might be that the file is saved on One Drive, and that is ruling over it's date settings?
That doesn't really help much without an explanation! Which date(s) is not as it/they should be?
I am able to add 01/09/2021 fine in your workbook.
AliGW on MS365 Insider (Windows) 32 bit
Sheet: Sheet1
That should make no difference. Do you mean OneDrive (personal) or SharePoint (business)? I have OneDrive and do not have this issue at all.Ohh it might be that the file is saved on One Drive, and that is ruling over it's date settings?
You can see on the picture below, when I download this workbook I just attached and it's on protected view, the dates on the table will show in the same format as the dates on the dialogue box on top. When I click on "Enable editing", it immediately inverts, on the dialogue box, the day and month. Beause of that, any day smaller than 12 will be converted to month automatically...
Untitled3.png
I can tell from the way that your date is displaying in the formula bar that your copy of Excel is set to the US date format: it is showing m/d/yyyy. If it were set to a UK setting, it would be dd/mm/yyyy.
Is it possible to change this?
In Excel:
File | Options ... | Language - check the settings there first.
Is this now resolved?
YES! Thank you
There were two "English" options, I just changed to the one which was not the one used with Windows. Go figure..
Fab!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks