datedif when earlier date is 19th century

%3CLINGO-SUB%20id%3D%22lingo-sub-1574720%22%20slang%3D%22en-US%22%3Edatedif%20when%20earlier%20date%20is%2019th%20century%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574720%22%20slang%3D%22en-US%22%3E%3CP%3Edatedif%20and%20%22y%22%20formula%20working%20with%20dates%20between%2020th%20and%2021st%20centuries%2C%20i.e.%201915%20to%202005.%26nbsp%3B%20When%20used%20for%20dates%20between%2019th%20and%2020th%20centuries%20these%20is%20error%20%22%23value!%22%26nbsp%3B%20i.e.%201889%20to%201990.%26nbsp%3B%20Solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1574720%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574744%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20when%20earlier%20date%20is%2019th%20century%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753429%22%20target%3D%22_blank%22%3E%40RockyARV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20only%20handles%20dates%20from%20January%201%2C%201900.%20If%20you%20enter%20for%20example%2001%2F01%2F1899%20in%20a%20cell%2C%20you%20will%20see%20that%20it%20is%20left-aligned%2C%20indicating%20that%20Excel%20sees%20it%20as%20text%20instead%20of%20as%20a%20valid%20date.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20perform%20calculations%20with%20dates%20before%20that%2C%20you%20could%20write%20user-defined%20functions%20(UDFs)%20in%20VBA.%20VBA%20handles%20older%20dates%20without%20problems.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%20see%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpre-1900-dates%2Fm-p%2F129999%22%20target%3D%22_blank%22%3Epre-1900%20dates%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574822%22%20slang%3D%22en-US%22%3ERe%3A%20datedif%20when%20earlier%20date%20is%2019th%20century%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753429%22%20target%3D%22_blank%22%3E%40RockyARV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJust%20in%20case%2C%20Power%20Query%20also%20works%20with%20pre-1900%20days.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@RockyARV 

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 

@RockyARV 

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