Date problems in Excel 365 (1900s Dates). Each time I enter a 2020, 2019, 2018, or 2017

%3CLINGO-SUB%20id%3D%22lingo-sub-1572747%22%20slang%3D%22en-US%22%3EDate%20problems%20in%20Excel%20365%20(1900s%20Dates).%20Each%20time%20I%20enter%20a%202020%2C%202019%2C%202018%2C%20or%202017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572747%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%20I%20was%20working%20without%20any%20problems%20until%20recently%20I%20created%20a%20new%20worksheet%20and%20no%20matter%20what%20formatting%20I%20use%20the%20dates%20come%20out%20to%20be%20something%20in%20the%201900s.%26nbsp%3B%20I%20have%20gone%20into%20the%20%22Advanced%22%20options%20and%20changed%20from%201900%20to%201904%20but%20it%20does%20not%20solve%20my%20problem.%26nbsp%3B%20I%20need%20help!%26nbsp%3B%20I%20am%20using%20the%20latest%20version%20of%20Microsoft%20365%2C%20and%20Windows%2010%20as%20my%20OS.%26nbsp%3B%20Have%20never%20had%20this%20issue%20before!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGeorge.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1572747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572822%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20problems%20in%20Excel%20365%20(1900s%20Dates).%20Each%20time%20I%20enter%20a%202020%2C%202019%2C%202018%2C%20or%202017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752479%22%20target%3D%22_blank%22%3E%40Gken091956%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20be%20more%20specific%2C%20what%20exactly%20do%20you%20enter%20and%20which%20result%20receive.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572830%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20problems%20in%20Excel%20365%20(1900s%20Dates).%20Each%20time%20I%20enter%20a%202020%2C%202019%2C%202018%2C%20or%202017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752479%22%20target%3D%22_blank%22%3E%40Gken091956%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20what%20is%20this%20your%20setting%20in%20Windows%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20468px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210978i50CF359037DB6EFD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574325%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20problems%20in%20Excel%20365%20(1900s%20Dates).%20Each%20time%20I%20enter%20a%202020%2C%202019%2C%202018%2C%20or%202017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574325%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%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20am%20entering%20transactions%20for%202020%20and%20I%20would%20enter%20010120%20(in%20day%2C%20month%2C%20year%20format%20but%20the%20value%20returned%20becomes%20%22September%2015%2C%201927.%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574807%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20problems%20in%20Excel%20365%20(1900s%20Dates).%20Each%20time%20I%20enter%20a%202020%2C%202019%2C%202018%2C%20or%202017%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752479%22%20target%3D%22_blank%22%3E%40Gken091956%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDates%20in%20Excel%20are%20sequential%20integer%20numbers%20starting%20from%201%20which%20is%20assigned%20to%20Jan%2001%2C%201900%20(for%20Windows).%20Thus%20if%20you%20enter%20number%2010120%20that%20will%20be%20Sep%2015%2C%201927%20and%20number%2044020%20is%20for%20Jul%2008%2C%202020.%20On%20Mac%20is%20the%20same%20story%2C%20but%20start%20is%20shifted%20on%204%20years.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20your%20regional%20format%20you%20shall%20enter%20dates%20in%20this%20format%2C%20like%2001%2F01%2F20.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear all, I was working without any problems until recently I created a new worksheet and no matter what formatting I use the dates come out to be something in the 1900s.  I have gone into the "Advanced" options and changed from 1900 to 1904 but it does not solve my problem.  I need help!  I am using the latest version of Microsoft 365, and Windows 10 as my OS.  Have never had this issue before!

 

George.

4 Replies

@Gken091956 

Could you please be more specific, what exactly do you enter and which result receive.

@Sergei Baklan 

For example, I am entering transactions for 2020 and I would enter 010120 (in day, month, year format but the value returned becomes "September 15, 1927."

@Gken091956 

Dates in Excel are sequential integer numbers starting from 1 which is assigned to Jan 01, 1900 (for Windows). Thus if you enter number 10120 that will be Sep 15, 1927 and number 44020 is for Jul 08, 2020. On Mac is the same story, but start is shifted on 4 years.

 

Depends on your regional format you shall enter dates in this format, like 01/01/20.