Excel 2007 Formatting Problem
Thread Starter
Join Date: Apr 2005
Location: Bexhill-on-Sea
Age: 77
Posts: 48
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Join Date: Apr 2005
Location: Earth
Posts: 366
Likes: 0
Received 0 Likes
on
0 Posts
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
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
Last edited by Spurlash2; 27th May 2011 at 18:28. Reason: edit solution!
Join Date: Jan 2008
Location: London, England
Age: 56
Posts: 300
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Jack's Granddad
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.
