Excel 2007 Formatting Problem
 

Hope some one can assist

I am constructing a spread sheet where a distance is divided by a speed to get a time taken.
As in A1(distance) / A2 (speed) = A3 (time), but I want the A3 cell displayed as hours and minutes not hours and decimals. I have formatted cell A3 to hh mm and the answer doesn't make sense, as in A1=134 miles divided by A2 9.58mph and the answer appears as 23hrs 41min, not 13hrs 59min.

If I format the A3 cell to "general" the answer is correct as 13.987..., but I want the answer as 13hrs 59min.

I have used the hh mm format on cells where the difference between two dates/ times are taken and displayed successfully as hh mm.



Any help would be appreciated.

Thanks
Merch

The reason you're having a problem is because the cell formats for date and time operate on a system time value. When zero this equates to midnight or 1st of January 1900. Each increment of 1 adds a day, i.e. 24 hours.

The value you are using being hours, is being counted as days, i.e. 24 times too much, so divide by 24.

A1=134
A2=9.58
A3=A1/A2/24
Format cell A3 to HH:MM:SS gives 13:59:15

Hope that helps.

Thanks Mac, it's so easy when someone tells me how. Works a treat
Thanks

The format [h]:mm is the one to use. You could also use [h]:mm:ss, for finer detail. This will also display correctly for larger hours and minute solutions. I think anything over 14 hours will display incorrectly unless you use the format I mention.

It is not a default format so you will need to do a Custom format. Right click a cell, Format Cells/Custom, and then type in as above.

Edit! this is for adding hours

That won't cure the initial problem Spurlash2.

Merch's initial problem was that he was generating numbers with base units of 'hours'. I.e 13.987 hours.

The date/time field formats (even custom ones) assume base units of 'days', hence the need to divide by 24, to convert his hours into days. I.e. his 13.987 becomes roughly 0.5828 days. When this is formatted with a date/time format, the answer then comes out correctly.

Mac,

Yes, went off half-cocked on the answer, which wasn't the answer, but was an answer for adding hours and minutes using the [h]:mm format.

Note to self: RTFQ.:O

Possibly not QUITE what you are after but if you format the cell A3 as h:mm then enter the formula (A1*60/A2)/1440, applying the figures you supply you get the result of 13:59 in A3.

(A1*60/A2)/1440
=> A1 * 60 / A2 / 1440
=> A1 / A2 * 60 / 1440
=> A1 / A2 / 24

Which is what I said.