Reg. Excel Calculation

Dear Support,

Can you give Solution for below.
Date Started Date Ended No.Of Years & Months
Sep-06 Jan-07 0 Years, 4 Months
Feb-07 Mar-08 1 Years, 1 Months
Apr-08 May-12 4 Years, 1 Months
Jun-12 Feb-14 1 Years, 8 Months
0 Years, 0 Months
Grand Total

Need formula for Grand Total for No of Years & Months


Dates Calculate.xlsx9.34 KB
Almir's picture

You need an array formula with sum of datedif

Enter this as an array formula (press CTRL+SHIFT+ENTER):

=DATEDIF(0,SUM(DATEDIF(B2:B5,D2:D5,"D")),"Y") & " Years, "
DATEDIF(0,SUM(DATEDIF(B2:B5,D2:D5,"D")),"YM") & " Months"

Explanation: formula first calculates date difference in days for each row, and then calculates total number of days. Then it calculates number of years and months from zero (0) to total number of days. It returns 7 years and 2 months.