Formatting the difference between two times (97)

Topic

New Reply

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

Reply | Quote

Replies

If you have 7:30 in A1 and 9:00 in A2 (that is formatted as [h]:mm), then try

=(A2-A1)*1440/60

in A3, formatted as general.

Reply | Quote

That worked! Thank you!

What exactly does the *1440/60 do ?

-Kelley

Reply | Quote

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.)

Reply | Quote

Thank you so much. That explains it well.

-Kelley

Reply | Quote

Simply there are 24 hours in a day, each hours counts 60 minutes: 24*60 = 1440. This means that by just doing (A2-A1)*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.

Reply | Quote