Excel and dates prior to 1900

  Neill 18:14 31 Jul 03
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 03

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 03

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 03

This should help click here

Also click here might be of interest.

  Neill 22:43 31 Jul 03

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

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

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…