# Thread: Time calculation!!! (Excel 2000 >)

1. ## 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)???

2. ## 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!

3. ## 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???

4. ## 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?

5. ## 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. ## 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!

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

10. ## 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. ## 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
•