Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Mar 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!
    Regards,
    Rudi

  3. #3
    New Lounger
    Join Date
    Mar 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Something like this?

    [attachment=82869:Capture1.JPG]

    Here's the formulas...
    [attachment=82868:Capture.JPG]
    Attached Images Attached Images
    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    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)
    Jerry

  6. #6
    New Lounger
    Join Date
    Mar 2009
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [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. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Attached Files Attached Files
    Regards
    Don

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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
    Regards,
    Rudi

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [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. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    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]
    Attached Images Attached Images
    Regards,
    Rudi

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

Posting Permissions

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