Forum Discussion
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 with a formula to calculate the period between on two date fields?
For example, count the number of records where the dates equal a two year period, three year period etc.
E.g. Column A (start date): 20200101; column B (end date): 20211231
What is the easiest way to work out the years (or months) between these two fields?
Thanks in advance 🙂
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.
- 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 🙂
