Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Best approach to display days, hours, minutes

    I have "a" method to display the days, hours and minutes resulting from subtracting two dates (with times). The result desired is like: 3 days, 2 hours, 10 minutes after the subtraction of mm/dd/yyyy hh:mm AM/PM, one from another..

    I'm wondering what the most efficient approach would be by formula (not VBA) for the result.

    Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    =CONCATENATE(DAY(A1)," Days ",HOUR(A1)," Hours ",MINUTE(A1)," Minutes")
    DHM.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about:
    =INT(A2-A1)&" days "&TEXT(MOD(A2-A1,1),"h"" hours"" m ""minutes""")

    Steve

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Better yet a custom format!
    CustomFormat.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The custom format using "d" (or "dd") is not elapsed days, but day of the month. It will allow only a maximum of 31 days after that it will start getting "weird" 32 - 60 days will read 1-29, then 61 - 91 will be 1-31, 92 - 121 will be 1-30, etc based on the days of the months...

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks, both of you. I did something very similar to Steve's. Guess that's the best approach.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Sorry, but I beg to differ. If you do a subtraction and custom format the cell it will return the proper values.
    CustomFormat.JPG
    HTH
    Last edited by RetiredGeek; 2013-08-29 at 15:41.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Put a difference that is 35 days apart and what do you get? It works if the difference is <=31 days (the number of days in January), but after this you get into February which starts over at Day1, so 25 days gives a day of only 4...

    Steve

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Et tu Brute!

    Thanks for setting me straight Steve
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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