Formula to sum the output from DATEDIF (X Years X Months X Days)

[DaveBre]

Hi all,

I'm currently using the DATEDIF formula below to work out the diffrence between two dates contained in B242 and C242.

=DATEDIF(B242,C242,"y")&" years "&DATEDIF(B242,C242,"ym")&" months "&DATEDIF(B242,C242,"md")&" days".

The output displays as X years X months X days which is fantastic for the end user reading the information.
Later in the sheet I need to sum a number of these outputs and again display that as X Years X Months X Days, but I can't get a formula to extract and sum the individual years, months and days.

Any ideas would be appreciated.

Thanks

Dave

 

[cyrilbrd]

Hi all,

I'm currently using the DATEDIF formula below to work out the diffrence between two dates contained in B242 and C242.

=DATEDIF(B242,C242,"y")&" years "&DATEDIF(B242,C242,"ym")&" months "&DATEDIF(B242,C242,"md")&" days".

The output displays as X years X months X days which is fantastic for the end user reading the information.
Later in the sheet I need to sum a number of these outputs and again display that as X Years X Months X Days, but I can't get a formula to extract and sum the individual years, months and days.

Any ideas would be appreciated.

Thanks

Dave

Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; vertical-align: middle; }.xl64 { text-align: center; vertical-align: middle; }.xl65 { color: rgb(102, 0, 102); font-weight: 700; font-family: "Courier New"; text-align: center; vertical-align: middle; }</style>

09-Feb-10	03-May-11		1 years 2 months 24 days
05-Jan-12	21-Feb-13		1 years 1 months 16 days

<colgroup><col style="width:65pt" span="4" width="65"> </colgroup><tbody>
</tbody>

in F1:
=DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"y")&" years "&DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"yM")&" month "&DATEDIF(A1,A1+((B2-A2)+(B1-A1)),"MD")&" days"
giving a value of

2 years 4 month 10 days

<colgroup><col></colgroup><tbody>
</tbody>

Would that work for you?

 

[DaveBre]

That works perfectly cyrilbrd, thank you very much for your help.

 

[cyrilbrd]

That works perfectly cyrilbrd, thank you very much for your help.

Thanks for the feedback.
You are most welcome.

 

[DaveBre]

Hi cyrilbrd,

Just wondering if you could answer one more question, I'm trying to trouble shoot the following formula which subtracts one total from another but I can't get it to work...

=DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"y")&"years"&DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"yM")&"month"&DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"MD")&" days"

I have the the rows 242-245 inclusive as one subtotal (displayed in 246) and 247-251 inclusive (displayed in 252). The formula above is in 253 and subtracts the totals of 252 from 246.

Any help would be appreciated.

Cheers

David

 

[cyrilbrd]

Please give me a sample such as what I posted in thread#2. Then I'll get back to you.

 

[DaveBre]

Hi cyrilbrd, thanks for replying.

It is related to the original post, so thanks for helping me out. Your answer to my original question worked well and I've no issues adding multiple date ranges together, however I've discovered that I need to subtract one range of dates from another.

In cell D250 I have the following formula which works:

=DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"y")&" years "&DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"yM")&" month "&DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"MD")&" days"

The output is shown as X years X Months X Days

In cell D256 I have the following formula which again works and shows the output as X years X Months X Days

=DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"y")&" years "&DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"yM")&" month "&DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"MD")&" days"

I need to take the output from D256 away from D250 and display the result in the same form, but I couldn't get the logic to work by using the following which I put together:

=DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"y")&"years"&DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"yM")&"month"&DATEDIF(B242,B242+((((C245-B245)+(C244-B244)+(C243-B243)+(C242-B242))))-DATEDIF(B247,B247+(((((C251-B251)+(C250-B250)+(C249-B249)+(C248-B248)+(C247-B247))))),"MD")&" days"

Any help would be appreciated.

Thanks

 

[cyrilbrd]

Ok, I understand formulae 1 and 2 references made to cells B246 to C255, But what is in in the formula 3? what are the rows 242 to 245? those are not part of the first ranges right?

I thought you wanted to subtract formula 1 from formula 2?

 

[cyrilbrd]

Given in B246:

6/1/11	2/2/12
7/1/11	3/3/12
7/31/11	4/2/12
8/30/11	5/2/12
		2 years 8 month 8 days
1/1/11	3/15/12
2/15/11	5/2/12
4/1/11	6/19/12
5/16/11	8/6/12
6/30/11	9/23/12
		6 years 1 month 2 days
		4 years -7 month -6 days

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

Formula to get the difference would be:
=DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"y")-DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"y")&" years "&DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"yM")-DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"yM")&" month "&DATEDIF(B251,B251+((((C255-B255)+(C254-B254)+(C253-B253)+(C252-B252)+(C251-B251)))),"MD")-DATEDIF(B246,B246+((((C249-B249)+(C248-B248)+(C247-B247)+(C246-B246)))),"MD")&" days"

Would that work for you?

 

[DaveBre]

cyrilbrd, thanks again. I've just tested the code and it works perfectly, I can see that I was missing the y / m / d from part of the formula.

Many Thanks

Cheers

David