Adding the Sum of 2 Date Ranges using DATEDIF Function (Showing in Years, Months, Days Format)

1
votes

I'm working on an Assignment where 2 different Date Ranges are available. I'm trying to add their differences and formatting them in XX Years, XX Months, XX Days (where XX is the Sum Number).

Now for a single range, I've searched on internet and Found DATEDIF Function useful like in Below image: Formula

=DATEDIF(Start_Date_1,End_Date_1,"y")&" Years, "&DATEDIF(Start_Date_1,End_Date_1,"ym")&" Months, "&DATEDIF(Start_Date_1,End_Date_1,"md")&" Days"

DATEDIF 1

It works Fine for 1 Date Range. But I've 2 different Ranges and 2 Different Differences. So I've tried to Sum the Each Parameter (Year, Month & Days) in the above formula Like Below: Formula:

=DATEDIF(Start_Date_1,End_Date_1,"y")+DATEDIF(Start_Date_2,End_Date_2,"y")&" Years, "&DATEDIF(Start_Date_1,End_Date_1,"ym")+DATEDIF(Start_Date_2,End_Date_2,"ym")&" Months, "&DATEDIF(Start_Date_1,End_Date_1,"md")+DATEDIF(Start_Date_2,End_Date_2,"md")&" Days"

DATEDIF 2

Now the Formula works fine until the Months/Days Range exceed 12/31 Respectively. As Seen in the picture, The months range has summed up to 13 while Days to 32. Now I'm looking for any solution or even Different approach to do the same formatting. Any Solution or Suggestion will be highly appreciated.

Thanks in Advance :).

1
excelexcel-formula

1 Answers

1
votes

If you define a month as 30 days you could just nestle IF's to compensate:

=IF(DATEDIF(Start_Date_1,End_Date_1,"md")+DATEDIF(Start_Date_2,End_Date_2,"md")>30,IF((DATEDIF(Start_Date_1,End_Date_1,"ym")+DATEDIF(Start_Date_2,End_Date_2,"ym")+1)>11,(DATEDIF(Start_Date_1,End_Date_1,"y")+DATEDIF(Start_Date_2,End_Date_2,"y")+1)&" Years, "&(DATEDIF(Start_Date_1,End_Date_1,"ym")+DATEDIF(Start_Date_2,End_Date_2,"ym")-11)&" Months, "&(DATEDIF(Start_Date_1,End_Date_1,"md")+DATEDIF(Start_Date_2,End_Date_2,"md")-30)&" Days",(DATEDIF(Start_Date_1,End_Date_1,"y")+DATEDIF(Start_Date_2,End_Date_2,"y"))&" Years, "&(DATEDIF(Start_Date_1,End_Date_1,"ym")+DATEDIF(Start_Date_2,End_Date_2,"ym")+1)&" Months, "&(DATEDIF(Start_Date_1,End_Date_1,"md")+DATEDIF(Start_Date_2,End_Date_2,"md")-30)&" Days"),DATEDIF(Start_Date_1,End_Date_1,"y")+DATEDIF(Start_Date_2,End_Date_2,"y")&" Years, "&DATEDIF(Start_Date_1,End_Date_1,"ym")+DATEDIF(Start_Date_2,End_Date_2,"ym")&" Months, "&DATEDIF(Start_Date_1,End_Date_1,"md")+DATEDIF(Start_Date_2,End_Date_2,"md")&" Days")

So IF days are over Thirty we check if months+1 are over 11, if both yes we minus 30 from days, 11 from months (11 to compensate a year plus the month from days) and add 1 to years, if months+1 aren't over 11 we simply add one to months and minus 30 from days, if neither are over the limits then we display the unadulterated formula.

(using your images as an example:

=IF(DATEDIF(A3,B3,"md")+DATEDIF(E3,F3,"md")>30,IF((DATEDIF(A3,B3,"ym")+DATEDIF(E3,F3,"ym")+1)>11,(DATEDIF(A3,B3,"y")+DATEDIF(E3,F3,"y")+1)&" Years, "&(DATEDIF(A3,B3,"ym")+DATEDIF(E3,F3,"ym")-11)&" Months, "&(DATEDIF(A3,B3,"md")+DATEDIF(E3,F3,"md")-30)&" Days",(DATEDIF(A3,B3,"y")+DATEDIF(E3,F3,"y"))&" Years, "&(DATEDIF(A3,B3,"ym")+DATEDIF(E3,F3,"ym")+1)&" Months, "&(DATEDIF(A3,B3,"md")+DATEDIF(E3,F3,"md")-30)&" Days"),DATEDIF(A3,B3,"y")+DATEDIF(E3,F3,"y")&" Years, "&DATEDIF(A3,B3,"ym")+DATEDIF(E3,F3,"ym")&" Months, "&DATEDIF(A3,B3,"md")+DATEDIF(E3,F3,"md")&" Days")

Will work for you)