Jan 14 2021 10:46 AM
Jan 14 2021 10:46 AM
My group has been using a shared excel work book for over a year and around the start on 2021 a new "bug?" has developed.
Our sheet has several columns that are formatted as dates. When we open this sheet in the a browser (Google Chrome) the date are correctly displayed as dates, however once an edit has been made to any part of the sheet, all of our 'date formatted cells' revert to being displayed as numbers. E.g. "14Jan2021" (mmdddyyyy) ==> "44210"
These cells are still formatted as dates, but remain displayed as numbers until I refresh the page. This bug is not seen when opened the sheet in the desktop app, and has only been noticed since the beginning of the year.
How can I stop this from happening?
Jan 14 2021 12:50 PM
I see second post today about this bug, but can't reproduce. Is that only when more than one person works with the file?
In any case I'd Send a Frown from Help->Feedback.
Jan 19 2021 09:56 AM
@Sergei Baklan Thank you!
This is a shared document and the problem is only present when opened in the browser.
It may help to know that these cells are within a table on the sheet, (with data validation parameter for these dates).
For example, the selected cell in column "H" is formatted as 'Date' and while the formula bar displays the date as "12/11/2020", the cell it's self displays "44176".
Note: We have recently increased the number of people using the sheet form 5 to 12. I'm considered that someone may have accidentally made a change that that has caused this problem, but I am flummoxed on what it could be.
Aug 25 2021 02:17 AM
Hello, I have the same issue, but the problem is visible only in desktop Excel. The cells contain a date (like 28.5.2021) but it is show a number instead (for example 44451). When I open this document in a browser (Chrome), the dates are show in a different format (DD/MM/YYYY) but at least it is readable.
No change of format helped. Where is the problem? Any ideas why is this happening?
Aug 25 2021 02:32 AMSolution
On desktop you shall apply Date format to the cells.
On Excel for web to change regional settings in File->Settings
Aug 25 2021 02:48 AM
Desktop - I've done this and it is not working. No reaction and no change. If I choose any formatting, but not change at all.
Browser - I've changed it and it works. In Desktop it is not reflected.
Aug 25 2021 03:05 AM
Desktop - please try to re-enter the value into the cell (F2->Enter)
Regional setting on web and desktop are not linked.
Sep 03 2021 12:07 PM
Sep 03 2021 02:07 PM
Excel for web keeps its regional setting on SharePoint/OneDrive where files are kept. Thus you have the same if you open it in any browser from any device.
Desktop version takes your PC/laptop setting, that's the only device such Excel knows about.
Oct 25 2021 02:52 PM
@Sergei Baklan I have tried this on desktop however I am still getting the number displaying as my date rather than the date itself. My cell is formatted as short date on the desktop. I changed the Regional Settings on the Web version and that displays correctly. Not sure what else to do now. Can you help? Thanks.
Oct 26 2021 03:06 AM
Oct 26 2021 04:00 AM
@Fabrice0966 Could you please attach workbook removing all other information but dates in question?
Oct 26 2021 02:47 PM
@Sergei Baklan I have attached the fields in question. You can see that the dates are formatted as such but only numbers are appearing. Thanks again for your help.
Oct 26 2021 02:50 PM
Oct 27 2021 12:30 AM
@Fabrice0966 @Sergei Baklan same on my side. I've downloaded your file, opened in desktop Excel and the dates are "corrupted". When I uploaded them to OneDrive or SharePoint and opened in a web browser (Chrome), everything was ok. See enclosed screenshot.
Really no clue where to search for the solution because this is not happening always. In my case the dates were one day OK, the other day where "corrupted."
Oct 27 2021 02:56 AM
If to uncheck Show Formulas
it works, please see attached.