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

D

DaveBre

New Member

Joined

May 4, 2013

 

Messages

11

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

ADVERTISEMENT

 

Excel Visio Data Visualizer Reaches General Availability - 2375

Excel Visio Data Visualizer Reaches General Availability - 2375

Excel Visio Data Visualizer Reaches General Availability - 2375

Excel Quick Takes Ten Excel Topics In 3 Minutes - 2419

Excel Quick Takes Ten Excel Topics In 3 Minutes - 2419

Yes!!! Define Your Own Linked Data Types in Excel! Episode 2378

Yes!!! Define Your Own Linked Data Types in Excel! Episode 2378

Three Ways to Reverse The Letters In An Excel Cell - 2391

Three Ways to Reverse The Letters In An Excel Cell - 2391

Hand Write Your Excel Data With The Action Pen - 2369

Hand Write Your Excel Data With The Action Pen - 2369

Excel Facts

Which Excel functions can ignore hidden rows?

 Click here to reveal answer

 Next

cyrilbrd

cyrilbrd

Well-known Member

Joined

Feb 2, 2012

 

Messages

4,071

 

Office Version

  1. 365

 

Platform

  1. Windows
  2. Mobile

DaveBre said:

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

Click to expand...



Given in A1:
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; }

09-Feb-1003-May-11 1 years 2 months 24 days
05-Jan-1221-Feb-13 1 years 1 months 16 days




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




Would that work for you?

D

DaveBre

New Member

Joined

May 4, 2013

 

Messages

11

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

cyrilbrd

cyrilbrd

Well-known Member

Joined

Feb 2, 2012

 

Messages

4,071

 

Office Version

  1. 365

 

Platform

  1. Windows
  2. Mobile

DaveBre said:

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


Thanks for the feedback.
You are most welcome.

D

DaveBre

New Member

Joined

May 4, 2013

 

Messages

11

ADVERTISEMENT

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

cyrilbrd

Well-known Member

Joined

Feb 2, 2012

 

Messages

4,071

 

Office Version

  1. 365

 

Platform

  1. Windows
  2. Mobile

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

D

DaveBre

New Member

Joined

May 4, 2013

 

Messages

11

ADVERTISEMENT

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

cyrilbrd

Well-known Member

Joined

Feb 2, 2012

 

Messages

4,071

 

Office Version

  1. 365

 

Platform

  1. Windows
  2. Mobile

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?

Last edited: Oct 29, 2013

cyrilbrd

cyrilbrd

Well-known Member

Joined

Feb 2, 2012

 

Messages

4,071

 

Office Version

  1. 365

 

Platform

  1. Windows
  2. Mobile

Given in B246:

6/1/112/2/12 
7/1/113/3/12 
7/31/114/2/12 
8/30/115/2/12 
  2 years 8 month 8 days
1/1/113/15/12 
2/15/115/2/12 
4/1/116/19/12 
5/16/118/6/12 
6/30/119/23/12 
  6 years 1 month 2 days
   
  4 years -7 month -6 days




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?