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
Regards,
sathireddy
| Attachment | Size |
|---|---|
| Dates Calculate.xlsx | 9.34 KB |
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.