Results 1 to 11 of 11

20040819, 09:36 #1
 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

20040819, 10:18 #2
 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

20040819, 11:31 #3
 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

20040819, 11:39 #4
 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

20040819, 12:44 #5
 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 builtin format. like it does for minutes [m] and hrs [h] to change from "day of month" to "elapsed days
Steve

20040820, 05:35 #6
 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

20040820, 06:14 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20040820, 09:10 #8
 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 "quickfingered" and meant perhaps?
=INT(A1)&":"&TEXT(A1,"hh:mm")
Steve

20040820, 09:33 #9
 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

20040820, 09:41 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20040820, 10:13 #11
 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 "fatfinger" since the "U" and "H" are right near each other, I don't know Dutch so I didn't guess that possibility.
Steve