datedif when earlier date is 19th century

Occasional Visitor

datedif and "y" formula working with dates between 20th and 21st centuries, i.e. 1915 to 2005.  When used for dates between 19th and 20th centuries these is error "#value!"  i.e. 1889 to 1990.  Solution?

2 Replies


Excel only handles dates from January 1, 1900. If you enter for example 01/01/1899 in a cell, you will see that it is left-aligned, indicating that Excel sees it as text instead of as a valid date.

If you want to perform calculations with dates before that, you could write user-defined functions (UDFs) in VBA. VBA handles older dates without problems.


Also see pre-1900 dates 


Just in case, Power Query also works with pre-1900 days.