Reputation: 2244

Identify Gap Years Between Multiple Dates in Excel

For example, I have these list of dates (These are of type 'date')

Sept 1, 2010
July 1, 2016
July 1, 2022

The gap years between these dates are 6 years. Another Example:

Sept 1, 2010
July 1, 2012
Sept 1, 2014
July 1, 2016

The gap years between these dates is 2. How will I make a formula to identify the gap years?

Upvotes: 0

Answers (3)

Reputation: 11998

Starting at your second date, just do =YEAR(A2)-YEAR(A1)

Upvotes: 1

Reputation: 64

You can create a new column:

    Gap_row = Previous Row - Current Row
    D2 = IFERROR(IF(D1<0;0;C2-C1);0)

Then create a pivot table for year and show Max(Gap_Row). The Grand total for Max of Year Gap is the Year Gap for those rows:

Upvotes: 1

Reputation: 61

You should check out DATEDIF function:

https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c

Upvotes: 1