Forum Discussion
Using dates preceding 1900 in excel
I thought that the cycle repeats every 100 years but trying the pattern didn't seem to be quite consistent. What appears to work is that adding 2000 years is the same as adding 730485 days.
So to get a datevalue (negative) for a date dd/mm/yyyy in the 1700s (say) add 2000 to the year and use
= DATE( 2000+yyyy, mm, dd ) - 730485
Going the other way adding 730485 to a negative serial date will show the correct day and month but 2000 years forward.
I thought that the cycle repeats every 100 years but trying the pattern didn't seem to be quite consistent. What appears to work is that adding 2000 years is the same as adding 730485 days.
So to get a datevalue (negative) for a date dd/mm/yyyy in the 1700s (say) add 2000 to the year and use
= DATE( 2000+yyyy, mm, dd ) - 730485
Going the other way adding 730485 to a negative serial date will show the correct day and month but 2000 years forward.