Adding Mulitple Date Dif with Years, Months, and Days

[chungiemo]

Hi

I found a solution to add mutiple date dif, and I wanted to amend the formula to include days, and having difficulty in understanding the formula after having a bash myself and looking for help.

I have attached the excel file that I found and greatly appreciate any assistance.

[rcm]

Are you standarizing all months to be 30 days?

[chungiemo]

Hi,

Good question though not sure, I was hoping to sum all the dates to give me total, it would be good be accurate but would easier making the months 30 days?

[kvsrinivasamurthy]

Pl give some Examples.

[samba_ravi]

E9

Please Login or Register  to view this content.

=SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("year",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0)+INT((SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("month",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0)+INT(SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("Day",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0)/30))/12)&" Year(s) "&MOD(SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("month",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0)+INT(SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("Day",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0)/30),12)&" Month(s) "&MOD(SUMPRODUCT(MID(SUBSTITUTE("0"&$D$9:$D9&"00 Year 00 Month 00 Day",","," "),SEARCH("Day",$D$9:$D9&"00 Year 00 Month 00 Day")-2,3)+0),30)&" Day(s)"
Try this and copy towards down

[chungiemo]

Hi Siva,

Many thanks for the reply, works a treat, don't really undertand it but I presume its looks at months as 30 days?

I will also mark it as solved.

Appreciate the assitance.

Man chun