Identify Gap Years Between Multiple Dates in Excel-CodePudding

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, 2026

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

CodePudding user response：

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:

CodePudding user response：

You should check out DATEDIF function:

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