1. I need some help please with two datedif issues.

I am using datedif to compute the time between two dates, ie.2006/02/09 to 2006/02/11. I am coming up with 00 yrs 00 mos 02 days. My issue is that my company counts this as working three days vice two days, so I need to know what adjustments to make to the formula.

After that issue is fixed, i need to add the total periods at the bottom of a column, for example i need to input the following individual periods then total them up to equal 00 yrs 06 months 23 days.
2006-06-07 to 2006-12-03 00 yrs 05 mos 27 days
2006-04-28 to 2006-05-09 00 yrs 00 mos 12 days
2006-04-01 to 2006-04-11 00 yrs 00 mos 11 days
2006-02-09 to 2006-02-11 00 yrs 00 mos 03 days
TOTAL 00 yrs 06 mos 23 days

thanks
brian

2. Hi Brian,

Firstly, Welcome to Woody's Lounge!

Since the REAL excel guru's do not seem to be around at this hour, let me ask some more details to help you solve this issue?

How do you need the output of your answer to be. If it is in the format that you suggest: as in ... 00 yrs 00 mos 02 days, then this will not be able to be calculated. This is a text output. If it can be simply a number...as in the number of days, then this can be calculated.

=DATEDIF(A1,A2,"d")+1 should solve for that extra day that your work requires.

Please give more details as to how the output should be so we can help a bit more. If it is possible, you could add a sample Excel attachment for even more clarity!

3. [quote name='Rudi' post='765417' date='14-Mar-2009 14:36']Hi Brian,

Firstly, Welcome to Woody's Lounge!

Since the REAL excel guru's do not seem to be around at this hour, let me ask some more details to help you solve this issue?

How do you need the output of your answer to be. If it is in the format that you suggest: as in ... 00 yrs 00 mos 02 days, then this will not be able to be calculated. This is a text output. If it can be simply a number...as in the number of days, then this can be calculated.

=DATEDIF(A1,A2,"d")+1 should solve for that extra day that your work requires.

Please give more details as to how the output should be so we can help a bit more. If it is possible, you could add a sample Excel attachment for even more clarity![/quote]

Sorry my file is at work and I am not allowed to bring it home.

I do not need it to output in this manner, just anything so that my employees can easily tell that one figure represents yrs, one for months and one for days

4. Something like this?

[attachment=82869:Capture1.JPG]

Here's the formulas...
[attachment=82868:Capture.JPG]

5. Assume the two dates are in cells A1 and A2 try:

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"m") & " month(s), " & DATEDIF(A1,A2,"md")+1 & " day(s)"

This will come in the format of 0 years, 1 month(s), 3 day(s)

6. [quote name='Jezza' post='765427' date='14-Mar-2009 16:51']Assume the two dates are in cells A1 and A2 try:

=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"m") & " month(s), " & DATEDIF(A1,A2,"md")+1 & " day(s)"

This will come in the format of 0 years, 1 month(s), 3 day(s)[/quote]

I THINK THAT'S IT!!!!

I will try it on my spreadsheets at work Monday.

brian

7. [quote name='bawhorton' post='765454' date='14-Mar-2009 21:21']I THINK THAT'S IT!!!!
brian[/quote]
If you want to sum the columns of days, months and years I believe that you will need to take the approach offered by Rudi with formatting providing the textual portion of your display, and a more complicated sum formula unless you are satisfied with 14 months and 68 days as a result. The attached file provides an example using 30 days as the standard month for summing purposes.

8. [quote name='bawhorton' post='765454' date='14-Mar-2009 21:21']I THINK THAT'S IT!!!!
brian[/quote]Further to my previous post; please note that there is no general approach which will provide the sum of the days to everyone's satisfaction.

9. Hey Don,

Thats a cool move you did on the formatting side of the formula. [thumbup] (I wish we still had that smilie!)

Also...some pretty nifty modifications on the sum function...to ensure they do not exceed the number of months and days! [thumbup] again

10. [quote name='Rudi' post='765476' date='15-Mar-2009 11:22']Hey Don,

Thats a cool move you did on the formatting side of the formula. [thumbup] (I wish we still had that smilie!)

Also...some pretty nifty modifications on the sum function...to ensure they do not exceed the number of months and days! [thumbup] again [/quote]Thanks Rudi
What technique did you use to include those spreadsheet images? I've not been able to figure that out for the new Lounge.

11. [quote name='wdwells' post='765480' date='15-Mar-2009 16:50']Thanks Rudi
What technique did you use to include those spreadsheet images? I've not been able to figure that out for the new Lounge.[/quote]
An image is just an attachment. Use Manage Current Atachments after browsing to the images and uploading them to insert them into the text of a post/reply.

12. [quote name='HansV' post='765516' date='15-Mar-2009 17:51']An image is just an attachment. Use Manage Current Atachments after browsing to the images and uploading them to insert them into the text of a post/reply.[/quote]
Thank you Hans
Got it.

13. Hi Don,

I tried to get this picture through to you soon after you asked, but for some reason the attachment bar just didn't want to finish "Uploading" the attachment. It seemed to get stuck on that little timer thing that goes about in circles. Its not the first time it happened?

Anyways...this is what Hans means... Ensure that you have your cursor in the message and then once you have uploaded the images, simply click on the small green icon on the left of the drop down to insert the desired...or all...of the attachments.

[attachment=82897:Capture1.JPG]

14. [quote name='Rudi' post='765547' date='16-Mar-2009 02:08']Hi Don,

I tried to get this picture through to you soon after you asked, but for some reason the attachment bar just didn't want to finish "Uploading" the attachment. It seemed to get stuck on that little timer thing that goes about in circles. Its not the first time it happened?

Anyways...this is what Hans means... Ensure that you have your cursor in the message and then once you have uploaded the images, simply click on the small green icon on the left of the drop down to insert the desired...or all...of the attachments.

[attachment=82897:Capture1.JPG][/quote]Thanks Rudi.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•