# assistance with a date based formula

Copper 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 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?

2 Replies

# Re: assistance with a date based formula

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.

# Re: assistance with a date based formula

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 :)