Forum Discussion
Excel 365 online, date formatting problem.
Ian,
Cell formatting is only to define the way how your dates will be displayed. To edit/input new date you have to use your regional date formatting as it defined in your environment (your desktop or O365 regional settings).
As for displaying the date - as soon as you transfer your file to another region the dates will be displayed in default formats of this region. Doesn't matter Excel online or desktop. If only you dind't force to keep regional format in custom format of the cell (asterisk or part like [$-en-GB] of the format string, more about that for example here https://www.ablebits.com/office-addins-blog/2015/03/11/change-date-format-excel/). Please note, Excel online itself doesn't support custom formatting, but it accepts custom formatting made in desktop application.
In brief, your users have to change/enter dates in the format they have in regional settings of their site, however you may display these dates in any format you prefer.
Hi Sergei,
I did use my regional date formatting as it defined in my environment (desktop and O365 regional settings). I set the range to UK on my desktop Excel, then when I started to edit it in the browser it has reverted to something different like Armenian. I set it again to UK and again I check it and it says its regional setting is Armenian. Therefore when we try to input date data it just gives us a #VALUE! error in column A, when editing in the browser. We are all editing from the UK.
It's fine in Excel but I cant have multiple editors then.
When editing in the browser, the only setting it will keep is English United States. Set it to anything else and it will not keep it.
Hi Ian,
Let skip Excel formatting for a while, just to be sure
- your Windows desktop regional setting (locale) is UK;
- your locale for OneDrive Site Settings is US;
Correct or something different?
Hi, I read you help others with formatin issues so maybe you can help me... We share a file with my colleagues and we´re using a personalized format for graphics axis. When I open the file in my computer the format is changed to General instead of keeping the original format as personalized. Any clue on how to solve it?
Hi Sergei,
Windows desktop regional setting (locale) is UK, yes. Also checked Excel is set to UK.
Locale for OneDrive Site Settings is ....... I cannot find this setting, is there a master setting somewhere?
- Hi Sergei,
I found the one drive regional settings and corrected it to UK, guess what, it all works again.
Thanks for the heads up on that, I was looking for a file setting like in Google sheets which were sheet specific.
So that was the answer, thanks again for your help.
