Forum Discussion
pre-1900 dates
I can find no help on how to tell Excel to properly format and calculate dates before 1900. I want to calculate age at death and have birth and death dates in columns. But Excel does not recognize ...
A very simple solution would be to just add an offset to both of the dates to be compared, so that a difference can then be calculated using the given Excel functions which start after 1900. 2000 years would include the repeating leap year dates, so should be a good value to offset.
Thus, 9/14/1752 12:00 am (date of Gregorian calendar acceptance) would be entered as 9/14/3752.
1/6/1980 12:00 am (0 time reference for GPS) would be entered as 1/6/3980.
The difference can then be calculated straightforward in Excel using the DAYS function, or just subtracting the earlier from the later date. (=83020 days, or 227.31x years at 365.24 days/yr).
Hi
How do you originally add any number to a date that is perceived as text?
It returns a #Value error
Please explain
Thanks
Nabil Mourad
To interpret the number that is entered as text (for pre-1900 dates) I assume you would have to use the text separator functions using the "/" symbols to separate the YYYY/MM/DD into individual elements YYYY, MM, DD. Then add 2000 to the pre-1900 YYYY to make it readable by Excel DATE functions. Then concatenate back to YYYY/MM/DD in the time shifted date. Then if want to do a comparison to other dates, could just offset the newer date +2000 years also before comparing them.
