Excel 365 online, date formatting problem.

Copper Contributor

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
Just spent two hours on hold with Microsoft and they cut me off!!!!!!!!!

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.

 

ian.png

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.

Hello,

 

Ok, in this case I can unfortunately not help. May be someone from MS can say more.

 

Best,

Mourad

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 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.

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?

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 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.


 

@cmhabc , did you changed OneDrive regional settings (not users ones)?

 I have the same problem.  My regional settings (One Drive, PC) are English UK.  The format of the cell is English UK but I have to input the dates in American format for them to display as UK format and to behave like an English date when sorting, etc.  I only have the problem when I browse online.  If I use proper Excel, it's how I want it to be.  If I create a new spreadsheet in Excel Online, date cells work how I expect them to, but with people sharing the sheet it's difficult to recreate it in Excel Online from scratch.  If I just create a new column in the existing spreadsheet, it behaves like the existing column.  This is very frustrating.

 

I have also an issue with Excel Online, which is not found in desktop

any of the following functions return "#VALUE!"

=MONTH(E$72&1)     <to convert month name to number>

=DATEVALUE(TODAY())

region settings in both online and desktop sets to "English (United States)"

 

@MagedMousa ,

 

In general first one works in Excel Online

image.png

Second one give an error since TODAY() returns the number and DATEVALUE takes the text as an argument.

Capture.PNG

 

@Sergei Baklan 

thank you very much for your update, I found that

=MONTH(A1&1)    -- return error

but

=MONTH(1&A1)   -- working fine

 

@MagedMousa , perhaps due to OneDrive regional settings, that's not only locale. But I didn't play with it.

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.