Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Time calculation!!! (Excel 2000 >)

    This is probably an easy question for some of you geniuses out there!
    See attachment for reference!
    I need a formula to calculate the difference between 2 dates in HH:MM.
    If I have a cell containing 18/08/2004 15:30 and another cell containing 19/08/2004 10:15, how can I work out the difference in HH:MM...(ie 23:45)???
    Regards,
    Rudi

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation!!! (Excel 2000 >)

    Hi, you just do a simple subtraction, and then format the cell with a customised time format - hh:mm

    see attach!
    Thanks,

    pmatz

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time calculation!!! (Excel 2000 >)

    Many thanks...
    This has since developed into another question! Once I have the hours value, say 37:45, how can I convert that back into days and hours.

    ie, My formula say returns 49:45. i now want another calc to convert this into days hours and minutes, 2:1:45....(2 days 1 hour and 45 min)...Any ideas???
    Regards,
    Rudi

  4. #4
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time calculation!!! (Excel 2000 >)

    Rudis, if you select the cell with the answer in (same calculation - one time minus the other) and hit CTRL 1 to go to format.

    Then go to the custom format. Here is where you can change the format of the answer, so dd:hh:mm will give you days, minutes and hours, whilst hh:mm will give you hours and minutes. You can free type these values where it says :Type: .

    The two date values which are being calculated give a result as a time/date value, so it is just a mtatter of formatting for your preference.

    Does this help?
    Thanks,

    pmatz

  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

    Re: Time calculation!!! (Excel 2000 >)

    dd:hh:mm format will only be good up to 31 days (Jan has only 31 days) and will then be "reset" to 1 (for Feb)

    AFAIK, Excel has no "elapsed" day built-in format. like it does for minutes [m] and hrs [h] to change from "day of month" to "elapsed days

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time calculation!!! (Excel 2000 >)

    Guys, thanx for your comments and suggestions.

    M?tz, (don't know where that character is??), you suggestion on formatting has satisfied my need, and I can get by with its returns, but as Steve has mentioned, I still am going to have temporary problems with the dd:hh:mm option. I see that if the time difference is more than say a month, the dd part loops back to 1 and not ie. 32!!!

    Is there any other possible solution to this, maybe by using functions or other calculative methods. If I could get any other suggestions from anyone I would highly appreciate it!!! I am still searching myself for a answer.

    For anyone that missed the question of this post: If I have a hours return from a calculation between 2 dates, I need the hours to display in DD:HH:MM format. IE say I have 49:30 (HH:MM), I want it to display 2:1:30 (DD:HH:MM), even if the date difference is more than a month??

    Any help is appreciated!
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time calculation!!! (Excel 2000 >)

    As Steve indicated, the formatting options that Excel provides do not support such a format, and it doesn't allow you to mix number and time formatting in the same cell. So the only workaround I see is to use a formula in another cell. Say that A1 contains the total time. In cell B1, enter this formula:
    <pre>=INT(A1)&":"&TEXT(A1,"uu:mm")</pre>

    The result is a text value, so it cannot be used in calculations; you must use A1 in calculations. But, if desired, you can hide A1.

  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

    Re: Time calculation!!! (Excel 2000 >)

    <hr>=INT(A1)&":"&TEXT(A1,"uu:mm")<hr>

    Perhaps you were too "quick-fingered" and meant perhaps?
    =INT(A1)&":"&TEXT(A1,"hh:mm")

    Steve

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thanx all :-)

    I will not be using the results in a calculation so the formula you have given is PERFECT!!!! It does exactly what I need!
    Regards,
    Rudi

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Time calculation!!! (Excel 2000 >)

    It should be "hh" indeed. I used ActiveCell.Formula to get the English version of the formula, but this is one of the rare situations in which VBA does NOT use US English consistently - the "u" is from the Dutch word for hour. Thanks for pointing it out.

  11. #11
    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

    Re: Time calculation!!! (Excel 2000 >)

    I assumed a "fat-finger" since the "U" and "H" are right near each other, I don't know Dutch so I didn't guess that possibility.

    Steve

Posting Permissions

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