About the problems encountered after changing the cell format to date on Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2825883%22%20slang%3D%22zh-CN%22%3EAbout%20the%20problems%20encountered%20after%20changing%20the%20cell%20format%20to%20date%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825883%22%20slang%3D%22zh-CN%22%3E%3CP%3EAbout%20the%20problems%20encountered%20after%20changing%20the%20cell%20format%20to%20date%20in%20Excel%20%3CBR%20%2F%3E%20Every%20time%20the%20data%20format%20is%20changed%20to%20the%20date%2C%20it%20will%20be%20displayed%20as%20%23.%20This%20problem%20can%20be%20solved%20by%20dividing%20into%20columns%20once%2C%20but%20after%20re-creating%20a%20new%20file%20and%20repeating%20the%20same%20operation%2C%20the%20same%20problem%20will%20still%20occur.%20%3CBR%20%2F%3E%20may%20I%20know%20what%20is%20the%20reason%3F%20%3CBR%20%2F%3E%20How%20could%20I%20solve%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EMy%20Office%20version%20is%20Microsoft%20365MSO%20(version%202109%20Build%2016.0.14430.20154)%2032-bit%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snipaste_2021-10-08_15-51-23.jpg%22%20style%3D%22width%3A%20782px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316155iC9F50F83D4FD4A4F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Snipaste_2021-10-08_15-51-23.jpg%22%20alt%3D%22Snipaste_2021-10-08_15-51-23.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snipaste_2021-10-08_16-00-12.jpg%22%20style%3D%22width%3A%20860px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316156iC78031734F4CDA76%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Snipaste_2021-10-08_16-00-12.jpg%22%20alt%3D%22Snipaste_2021-10-08_16-00-12.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2825883%22%20slang%3D%22zh-CN%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825969%22%20slang%3D%22en-US%22%3ERe%3A%20About%20the%20problems%20encountered%20after%20changing%20the%20cell%20format%20to%20date%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179454%22%20target%3D%22_blank%22%3E%40YWChen%3C%2FA%3E%26nbsp%3BWell%2C%20Excel%20stores%20dates%20as%20sequential%20numbers%20starting%20from%20January%201%2C%201900.%20Then%20number%2020211008%20formatted%20as%20a%20date%20refers%20to%20a%20data%20some%2055%20thousand%20years%20into%20the%20future.%20That's%20beyond%20the%20acceptable%20limits%20for%20Excel%2C%20hence%20%23%23%23%23%23%23%23%23%23%23.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

About the problems encountered after changing the cell format to date in Excel
Every time the data format is changed to the date, it will be displayed as #. This problem can be solved by dividing into columns once, but after re-creating a new file and repeating the same operation, the same problem will still occur.
may I know what is the reason?
How could I solve this problem?

 

My Office version is Microsoft 365MSO (version 2109 Build 16.0.14430.20154) 32-bit

Snipaste_2021-10-08_15-51-23.jpgSnipaste_2021-10-08_16-00-12.jpg

1 Reply

@YWChen Well, Excel stores dates as sequential numbers starting from January 1, 1900. Then number 20211008 formatted as a date refers to a data some 55 thousand years into the future. That's beyond the acceptable limits for Excel, hence ##########.

 

So, you need to enter the data so that is is recognised as one. On my system I can just type 8oct and it automatically gets translated to 2021-10-08 in a date format. Not sure how that would be in Chinese though.