Excel Number Formatting

Question

Hi, I have a weird problem in one of my excel files (not all, just one of them), which when I open it a yesterday, all number formats in all cells in all worksheets were changed to "Date" format. I did change all number formats in each worksheet back to "general" one by one yesterday, and it was working fine when I reopened it. But when I do a "clear format" in any of the cells, the number formats in the selected cells were changed back to "date". In fact if I clear format of the whole worksheet, every cells were changed to "date" format. This problem did not happen when I edited the file last week and it suddenly turned up yesterday. I searched the internet in hope for a solution but nothing useful turns up. I tried opening a new excel file and do the same "clear format" trick but nothing happens and the number formats are still "general". So this problem only happens in one exact file. Appreciate any help, thanks.

Answer 1

There is a bug in Excel whereby under certain circumstances (I've never seen a specific cause pinned down) the number format of the Normal cell style gets changed to Date. You can just modify the cell style to put it back to General rather than going cell by cell.

Answer 2

The original file is a confidential document but I could made a new empty sheet and copy to a new book. The problem is still present in the new book. Weirdly though when I opened it in web excel the problem was gone, but if I download it to my computer the problem is still there. Below is the share link to the file in my onedrive.

https://1drv.ms/x/s!AluxzvFpXRvvgc5N-eDb4L0K-UEPqg?e=CdjTMq

Answer 3

May you give a sample data to analyze the issue much better?

Answer 4

Thanks for the reply. I am certainly aware of the ways mentioned that circumvent the problem however, those methods sadly do not solve the problem. I can surely put a ' or space before EVERY value I entered but when I do the "clear formats" it still give me a "date" format on EVERY cell. It is almost like this file has a default format or some sort set to "date" format that whenever I do clear formats it doesn't give me a "general" but a "date" format even in an empty cell.

Answer 5

Hi ChiuKwan, hope you're doing well. I’m Ian, and I’m happy to help you today.

You can pre-format your cell to avoid this automatic change from number to date.

You can follow the article below to pre-format your cell.

https://support.microsoft.com/topic/stop-automa...

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.