Can Excel Handle Dates Before 1900 (Yes!) | Difference in Years, Month or Days

Hi Chester, my question is is it possible that the pre-1900 date in column D can exist in a format appropriate for ascending (descending) sort options of dates? The date format is impossible by excel, the text format doesn't sort dates properly.

Please note that adding 1000 years would, in many cases, cause issues with leap years AND return wrong weekdays etc. The gregorian calendar resets completely after every 400 years, so you should rather add 2000 years instead. After that, you should get correct results with any date-related calculation whenever using Gregorian calendar. Using Julian calendar with this will yield erroneous results.

You will still run into errors, however, because Excel cannot handle dates past 9999-12-31, or 7999-12-31 in this case, but we rarely need such dates anyway so it should be okay! Also, you cannot go below 99 BC (= year -100 CE) simply because you will, again, run into Excel's inability to handle dates before 1900...

👏👏👏👏👍👍👍

This is helpful. Thx! Keep up the great work!

By adding 1000 years and calculating differences between dates, are leap years still handled correctly? At this moment I can't wrap my mind around that potential issue.

Clever! Thanks Chester!!

I need a format that update dates in every month to that months date
Can you help me on that

Essential tutorial. The explanation with the treatment with dates prior to 1900 is very good. Thanks Chester.

Wow. I've used DATEDIF many times, but I had no idea you could use "YM" and "MD". That is soooooo helpful!
I wonder why MS refuses to properly support/incorporate the DATEDIF function into Excel? As in, no IntelliSense, and warnings of deprecation.
I know it was a Lotus crossover function, but my gosh, it is so useful - just fully adopt it, Microsoft!!!