Home

Dates incorrect

%3CLINGO-SUB%20id%3D%22lingo-sub-481361%22%20slang%3D%22en-US%22%3EDates%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481361%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EWhen%20I%20type%20in%2001012017%20into%20a%20column%20with%20a%20short%20date%20format%20of%20dd%2Fmm%2Fyyyy%2C%20I%20get%26nbsp%3B22%2F10%2F4674%3CBR%20%2F%3Eand%20if%20I%20type%20in%2001022017%2C%20I%20get%2010%2F03%2F4702%20-%20I%20have%20changed%20the%20format%20of%20the%20date%20and%20each%20format%20is%20producing%20some%20very%20wacky%20and%20disconnected%20dates!%26nbsp%3B%20When%20I%20convert%20the%20cells%20to%20number%20I%20get%20the%20original%20typed%20number%20less%20the%20leading%20zero%2C%20ie%201012017%3C%2FP%3E%3CP%3EWhat%20is%20going%20on%3F%26nbsp%3B%20My%20PC%20has%20the%20correct%20system%20date%20set.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-481361%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481490%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325747%22%20target%3D%22_blank%22%3E%40myers2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20number%20you%20type%20is%20taken%20to%20be%20the%20number%20of%20days%20since%20the%20beginning%20of%20the%20year%201900.%3C%2FP%3E%3CP%3EHence%2C%201022017%20(days)%20is%20roughly%202804%20years.%26nbsp%3B%20Add%20the%20base%20value%20of%201900%20and%20you%20find%20yourself%20in%20the%20year%204704.%26nbsp%3B%20Enjoy%20it%20while%20it%20lasts%20%3A-)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-481398%22%20slang%3D%22en-US%22%3ERe%3A%20Dates%20incorrect%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-481398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325747%22%20target%3D%22_blank%22%3E%40myers2020%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDates%20in%20Excel%20are%20actually%20integers%20numbers%20starting%20from%201%20which%20is%20Jan%2001%2C%201900.%20Thus%2C%20for%20example%2C%20Apr%2023%2C%202019%20is%20equal%20to%2043578.%20When%20you%20enter%20much%20larger%20number%20you%20receive%20the%20far%20away%20date.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormatting%20is%20only%20to%20make%20date%20more%20human%20friendly%20to%20operate%2C%20they%20are%20integer%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
myers2020
Occasional Visitor

Hi,

When I type in 01012017 into a column with a short date format of dd/mm/yyyy, I get 22/10/4674
and if I type in 01022017, I get 10/03/4702 - I have changed the format of the date and each format is producing some very wacky and disconnected dates!  When I convert the cells to number I get the original typed number less the leading zero, ie 1012017

What is going on?  My PC has the correct system date set.

2 Replies

@myers2020 ,

 

Dates in Excel are actually integers numbers starting from 1 which is Jan 01, 1900. Thus, for example, Apr 23, 2019 is equal to 43578. When you enter much larger number you receive the far away date.

 

Formatting is only to make date more human friendly to operate, they are integer numbers.

@myers2020 

The number you type is taken to be the number of days since the beginning of the year 1900.

Hence, 1022017 (days) is roughly 2804 years.  Add the base value of 1900 and you find yourself in the year 4704.  Enjoy it while it lasts :-)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies