Forum Discussion
Excel 365 online, date formatting problem.
Dear all,
I have a problem with date formatting, I think it is a bug in the program but I'm not sure.
When editing in Excel proper, in the attached file there is a formula in column A which reads data from column B "(=YEAR(B1)+(WEEKNUM(B1)/100))". When 15/02/2017 is added to column B, I get 2017.07, which is exactly what I want.
Column B is formatted for UK date system e.g. 15/02/2017.
The Problem
When I'm editing the sheet in my browser (and I want to do this for multiple user sharing reasons), I get #VALUE! in column A. When I look at the formatting of column B it has changed itself to Armenian or Azerbaijani, no longer UK format. If I change it again to UK, click out of the cell, then right click back into it, select number format, it has changed itself again to Armenian again.
If I write the same date as an American format e.g. 02/15/2017 it works ok, but try telling everyone they have to use a special date system for this one sheet.
Any ideas on how to lock the formatting when editing the sheet in a browser window?
19 Replies
Hey Ian Williams,
I believe I solved the issue. After checking my computer and OneDrive regional settings and confirming they were correct, I also thought to check my SharePoint regional settings.
I opened the SharePoint location where my Excel document was stored and went to regional settings and found it set to somewhere in south America.
After correcting this the issue was resolved.
you do not need to do anything except making two changes
1. your ODB site Local settings to English (united kingdom). ODB Site > Settings >> Regional settings >> Local >> English (united kingdom)
2. your Delve settings delve >> ME >> Update profile > "How can i change language and regional settings" >> language and Region >> Local >> English (united kingdom)
regards,
Akki
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?
Hello Ian,
is the file, you attached, the original file before you uploaded it to Excel Online? If so, then I noticed that the date format includes a locale set to Armenian, when opening the Format Cells dialogue. Please see the screenshot below. May be (and may be) that's the reason, why it changes to Armenian in Excel Online. I did not test it in Excel Online, as I have different language settings.
Best,
Mourad
The file is as downloaded from Excel online, the formatting to Armenian happens only when working online, I set it to English United Kingdom, then when you check it again after setting it, it reverts to Armenian again.
This only happens when the file is being edited in the browser, it is ok when being edited in Excel, but as I need the function for an online sheet so that multiple users can work simultaneously, its not much good. Back to Google sheets I think.Ian Williams did you manage to sort this out. We have the same issue. We have an excel file with travel dates in it and for online viewing it always reverts back to armenian and if I change it still reverts back. Its really annoying all 3 users. We are having to set up the cells at text fields. All three users (including the file owner) have changed our regional settings the english uk.
IT department are also confused, and nobody (it seems) has a solution.
One drive is just not that easy at the minute.
Ian Williams wrote:The file is as downloaded from Excel online, the formatting to Armenian happens only when working online, I set it to English United Kingdom, then when you check it again after setting it, it reverts to Armenian again.
This only happens when the file is being edited in the browser, it is ok when being edited in Excel, but as I need the function for an online sheet so that multiple users can work simultaneously, its not much good. Back to Google sheets I think.
- Just spent two hours on hold with Microsoft and they cut me off!!!!!!!!!
