SOLVED

Excel does not convert numbers to date

%3CLINGO-SUB%20id%3D%22lingo-sub-2115598%22%20slang%3D%22en-US%22%3EExcel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115598%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Excel%20with%20Office365%20newly%20installed%20I%20notice%20that%20numbers%20are%20no%20more%20converted%20automatically%20to%20dates.%20Also%20when%20I%20change%20the%20cell%20format%20to%20a%20specific%20date%2C%20it%20never%20changes%20automatically%20as%20per%20the%20format%20set.%20Same%20with%20every%20new%20spreadsheet%20I%20create.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20you%20have%20any%20hint%20about%20this%20issue%20please%3F%20Reading%20over%20the%20Internet%20it%20seems%20this%20feature%20cannot%20be%20disabled%20so%20I%20assume%20it%20is%20a%20defect%20with%20my%20setup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2115598%22%20slang%3D%22en-US%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-2117046%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2117046%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F956899%22%20target%3D%22_blank%22%3E%40David_Sword%3C%2FA%3E%26nbsp%3BGreat%20to%20know%20you%20sorted%20this%20out%2C%20thank%20you%20for%20the%20update%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116902%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116902%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%3Bthanks%20for%20your%20help%2C%20I%20have%20switched%20the%20OS%20date%20format%20for%20French%20(Switzerland)%20and%20it%20is%20now%20capable%20of%20recognizing%20even%201.2%20as%20February%201st%20successfully%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115698%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F956899%22%20target%3D%22_blank%22%3E%40David_Sword%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20default%20date%20format%20is%20dd%2Fmm%2Fyyyy.%3C%2FP%3E%0A%3CP%3EIf%20you%20enter%201%2F2%2F2021%20it%20shall%20be%20recognized%20as%20date%20(01%20Feb%202021).%20If%20you%20enter%201.2.2021%20it%20doesn't%20match%20your%20date%20format%20and%20Excel%20takes%20it%20as%20text%20(%20like%20%3CEM%3Eaas%2Cdjas%3C%2FEM%3E%2C%20Excel%20also%20doesn't%20recognize%20it).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115640%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115640%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%3B%20I%20am%20running%20Windows%2010%2064bits%20see%20the%20attached%20Geo%20and%20date%20format%20settings.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115615%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F956899%22%20target%3D%22_blank%22%3E%40David_Sword%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20depends%20on%20your%20operational%20system%20(Windows%2C%20iOS%2C%20etc%2C%20have%20no%20idea%20on%20which%20one%20you%20are)%20regional%20settings%2C%20please%20check%20default%20date%20format%20for%20the%20OS%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2115612%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20does%20not%20convert%20numbers%20to%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2115612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F956899%22%20target%3D%22_blank%22%3E%40David_Sword%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20the%20system%20date%20format%20on%20your%20computer%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

Using Excel with Office365 newly installed I notice that numbers are no more converted automatically to dates. Also when I change the cell format to a specific date, it never changes automatically as per the format set. Same with every new spreadsheet I create.

 

Would you have any hint about this issue please? Reading over the Internet it seems this feature cannot be disabled so I assume it is a defect with my setup.

 

Thanks in advance

6 Replies

@David_Sword 

What is the system date format on your computer?

@David_Sword 

It depends on your operational system (Windows, iOS, etc, have no idea on which one you are) regional settings, please check default date format for the OS

@Sergei Baklan  I am running Windows 10 64bits see the attached Geo and date format settings.

best response confirmed by David_Sword (New Contributor)
Solution

@David_Sword 

Your default date format is dd/mm/yyyy.

If you enter 1/2/2021 it shall be recognized as date (01 Feb 2021). If you enter 1.2.2021 it doesn't match your date format and Excel takes it as text ( like aas,djas, Excel also doesn't recognize it).

@Sergei Baklan thanks for your help, I have switched the OS date format for French (Switzerland) and it is now capable of recognizing even 1.2 as February 1st successfully

@David_Sword Great to know you sorted this out, thank you for the update