assistance with a date based formula

Copper Contributor

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

2 Replies


If A2 and B2 contain dates (recognized by Excel as dates):

The number of years between A2 and B2 is


The number of months between A2 and B2 is



But if the cells contain for example 20200101 as a number, Excel will not recognize them as dates, s the formula becomes more complicated:







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




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