Results 1 to 6 of 6

20011010, 16:15 #1
 Join Date
 Jan 2001
 Posts
 50
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formatting the difference between two times (97)
I have a formula that is calculating the difference between two times. For example, if you take 7:00 from 9:30, then I'll get 2:30 for the answer if the cell is formatted as a time. However, I don't want it to say 2:30, I want it to show 2.5 for 2 and a half hours. I'm sure there is a way to do this, but I'm just not sure how. Any help would be most appreciated.
Kelley

20011010, 16:22 #2
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formatting the difference between two times (97)
If you have 7:30 in A1 and 9:00 in A2 (that is formatted as [h]:mm), then try
=(A2A1)*1440/60
in A3, formatted as general.

20011010, 16:27 #3
 Join Date
 Jan 2001
 Posts
 50
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formatting the difference between two times (97)
That worked! Thank you!
What exactly does the *1440/60 do ?
Kelley

20011010, 16:40 #4
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Formatting the difference between two times (97)
It does the same as "*24". Times are maintained in Excel as a decimal portion of a day, so either *1440/60 or *24 will convert to hours. (1440 minutes in a day.)
John ... I float in liquid gardens
UTC 7ąDS

20011010, 16:42 #5
 Join Date
 Jan 2001
 Posts
 50
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formatting the difference between two times (97)
Thank you so much. That explains it well.
Kelley

20011010, 16:43 #6
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formatting the difference between two times (97)
Simply there are 24 hours in a day, each hours counts 60 minutes: 24*60 = 1440. This means that by just doing (A2A1)*24, the result is exactly the same. The reason for 1440/60 is that if you want to convert the difference in minutes, then you should only multiply by 1440; if you want the difference in hours, then you just multiply by 1440/60 or 24 and format as general. In that context, 1440/60 is more logical to use than 24.