Excel and dates prior to 1900

  Neill 18:14 31 Jul 2003
Locked

I know that taking one date away from another will calculate the numbers of days in between but can I calculate length of time when either one date or both dates are before 1900?
Thanks

  VoG II 19:09 31 Jul 2003

If one date is before 1900 and one is after you will get an answer 1 day too large. This is because of a (deliberate) bug in Excel that treats 1900 as if it was a leap year (which it wasn't).

A deliberate bug? Microsoft copied the bug from Lotus 1-2-3 to ensure compatibility between the two spreadsheets. Amazing but true.

  Neill 19:29 31 Jul 2003

Am I missing something obvious? I get #VALUE! when taking one date away from the other. Should I be formatting the dates in a particular way? When typing 31/12/1890 for example the date ends up at the left of the cell so I assume Excel thinks it is text hence the error result.

  VoG II 19:35 31 Jul 2003

This should help click here

Also click here might be of interest.

  Neill 22:43 31 Jul 2003

I go for the first example! Thanks for your help once again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?