Forum Discussion
Aliscar
Feb 14, 2023Copper Contributor
assistance with a date based formula
Hi there, I will preface this request with a confession that I am not very sophisticated with Excel and tend to use simple formulas. That said, I am trying to expand my skills. Can anyone help wit...
HansVogelaar
Feb 14, 2023MVP
If A2 and B2 contain dates (recognized by Excel as dates):
The number of years between A2 and B2 is
=DATEDIF(A2,B2,"Y")
The number of months between A2 and B2 is
=DATEDIF(A2,B2,"M")
But if the cells contain for example 20200101 as a number, Excel will not recognize them as dates, s the formula becomes more complicated:
=DATEDIF(DATE(QUOTIENT(A2,10000),QUOTIENT(MOD(A2,10000),100),MOD(A2,100)),DATE(QUOTIENT(B2,10000),QUOTIENT(MOD(B2,10000),100),MOD(B2,100)),"Y")
and
=DATEDIF(DATE(QUOTIENT(A2,10000),QUOTIENT(MOD(A2,10000),100),MOD(A2,100)),DATE(QUOTIENT(B2,10000),QUOTIENT(MOD(B2,10000),100),MOD(B2,100)),"M")
Warning:
From 2020-01-01 to 2021-12-31 will be counted as 1 year, not 2 years, and as 23 months, not 24 months. If you want to count it as 2 years or 24 months, use
=DATEDIF(A2,B2+1,"Y")
and
=DATEDIF(A2,B2+1,"M")
It's similar for the more complicated versions.
Aliscar
Feb 14, 2023Copper Contributor
thank you! this makes sense (at least the first part of the response) - I'll give it a try and see how I go. Really appreciate you taking the time to explain the formula, Hans 🙂