Dates incorrect

Copper Contributor

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 :)