SOLVED

Excel 365 online, Formatted as dates, but displayed as numbers?

%3CLINGO-SUB%20id%3D%22lingo-sub-2061899%22%20slang%3D%22en-US%22%3EExcel%20365%20online%2C%20Formatted%20as%20dates%2C%20but%20displayed%20as%20numbers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061899%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20group%20has%20been%20using%20a%20shared%20excel%20work%20book%20for%20over%20a%20year%20and%20around%20the%20start%20on%202021%20a%20new%20%22bug%3F%22%20has%20developed.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOur%20sheet%20has%20several%20columns%20that%20are%20formatted%20as%20dates.%26nbsp%3B%20When%20we%20open%20this%20sheet%20in%20the%20a%20browser%20(Google%20Chrome)%20the%20date%20are%20correctly%20displayed%20as%20dates%2C%20however%20once%20an%20edit%20has%20been%20made%20to%20any%20part%20of%20the%20sheet%2C%20all%20of%20our%20'date%20formatted%20cells'%20revert%20to%20being%20displayed%20as%20numbers.%26nbsp%3B%20E.g.%20%2214Jan2021%22%20(mmdddyyyy)%20%3D%3D%26gt%3B%26nbsp%3B%20%2244210%22%3C%2FP%3E%3CP%3EThese%20cells%20are%20still%20formatted%20as%20dates%2C%20but%20remain%20displayed%20as%20numbers%20until%20I%20refresh%20the%20page.%26nbsp%3B%20This%20bug%20is%20not%20seen%20when%20opened%20the%20sheet%20in%20the%20desktop%20app%2C%20and%20has%20only%20been%20noticed%20since%20the%20beginning%20of%20the%20year.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20stop%20this%20from%20happening%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2061899%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2062281%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20online%2C%20Formatted%20as%20dates%2C%20but%20displayed%20as%20numbers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2062281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F929505%22%20target%3D%22_blank%22%3E%40Will_Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see%20second%20post%20today%20about%20this%20bug%2C%20but%20can't%20reproduce.%20Is%20that%20only%20when%20more%20than%20one%20person%20works%20with%20the%20file%3F%3C%2FP%3E%0A%3CP%3EIn%20any%20case%20I'd%20Send%20a%20Frown%20from%20Help-%26gt%3BFeedback.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2075727%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20online%2C%20Formatted%20as%20dates%2C%20but%20displayed%20as%20numbers%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2075727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20shared%20document%20and%20the%20problem%20is%20only%20present%20when%20opened%20in%20the%20browser.%3C%2FP%3E%3CP%3EIt%20may%20help%20to%20know%20that%20these%20cells%20are%20within%20a%20table%20on%20the%20sheet%2C%20(with%20data%20validation%20parameter%20for%20these%20dates).%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20the%20selected%20cell%20in%20column%20%22H%22%20is%20formatted%20as%20'Date'%20and%20while%20the%20formula%20bar%20displays%20the%20date%20as%20%2212%2F11%2F2020%22%2C%20the%20cell%20it's%20self%20displays%20%2244176%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Will_Excel_0-1611077800295.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247510iAD7A1D3451106D6A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Will_Excel_0-1611077800295.png%22%20alt%3D%22Will_Excel_0-1611077800295.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20We%20have%20recently%20increased%20the%20number%20of%20people%20using%20the%20sheet%20form%205%20to%2012.%26nbsp%3B%20I'm%20considered%20that%20someone%20may%20have%20accidentally%20made%20a%20change%20that%20that%20has%20caused%20this%20problem%2C%20but%20I%20am%20flummoxed%20on%20what%20it%20could%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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? 

16 Replies

@Will_Excel 

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.

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

 

Will_Excel_0-1611077800295.png

 

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.

@Will_Excel 

 

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?

 

Thanks!

best response confirmed by Will_Excel (New Contributor)
Solution

@ts84103 

On desktop you shall apply Date format to the cells.

On Excel for web to change regional settings in File->Settings

@Sergei Baklan 

 

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.

?

 

@ts84103 

Desktop - please try to re-enter the value into the cell (F2->Enter)

 

Regional setting on web and desktop are not linked.

Thank you!
I was not aware that 365 had it's own "Languiage and Region" settinges. While (for me) this issue had resolved it's self by March 2021, I now have entered my prefered "Regional format" for the dates and times.

@Will_Excel 

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.

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

@Fabrice0966 Do you numbers on Desktop or on Web?

The numbers appear on the desktop. On web it is appearing correctly as a date format. Thanks

@Fabrice0966 Could you please attach workbook removing all other information but dates in question?

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

@Sergei Baklan well that's interesting. The uploded attachment shows the dates being correctly formatted but on my SharePoint and Desktop it doesn't. Not sure if that can be explained as to what is happening here?

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

 

@Fabrice0966 

If to uncheck Show Formulas

image.png

it works, please see attached.