Did you know that Excel cannot record a date before 1 Jan 1900?
Excel Is Simple
Did you know that in excel you can’t enter any date which is before 1st Jan 1900? Well this is true because excel uses the 1900 date system.
So, let’s first try to understand how does excel store dates. Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1 and every date post that is the number of days added to this date. You try to add any date before 1 jan 1900; excel will NOT recognise it in the date format. Clearly means that most of the historical dates before 1900 that have been entered in excel are in the text format.
On the other hand Google sheets also uses a similar system but it seems to use 31st Dec 1899 as Day one and at the same time anything before that gives you a negative value. Means that it will still store the date as a number and not as a text value. Sadly when you download the google sheet as an excel sheet, the date will be converted to text.
If you work with excel sheets coming from many sources, you can see that the date formats are all messed up. There are various types of date formats that can be used by people which can be confusing if the date is less than 12.
14951142 Bytes