Displaying times correctly using time/speed/distance formula

[dpbarry]

Hi Folk..

Having a bit of a problem displaying calculated times using the distance speed formula

To calculate a time, I use (distance*60)/speed and get the resultant time in minute but how can I format the display to show it in hours:mins:secs

eg..
for 60mins, I'd like it to display 01:00:00
for 90min, I'd like it to display 01:30:00

Assistance greatly appreciated

Declan

 

[MARK858]

When you click the cell what exactly is appearing in the formula bar?

 

[MARK858]

Sorry I should have asked is...
When you click the cell what exactly is appearing in the formula bar and the cell (with the format set as General)?

 

[dpbarry]

Sorry I should have asked is...
When you click the cell what exactly is appearing in the formula bar and the cell (with the format set as General)?

Hi Mark585

In the formula Bar I have =(A6*60)/$F$2

A6 is the distance (4 Miles) , and $F$ is the speed (10 knots). The value I get is 24 minutes which is correct (cells formatted as General)

but I would like it displayed in the format 00:24:00

18 miles at a speed of 10 knots shows up as 108 minutes which is correct but I would like it displayed as 01:48:00 (1 hour, 48 minutes)

I'm looking to build up an A6 size table of speed/time/distance so that I can make a quick glance at the table without having to make mental adjustments.

Cheers

Declan

 

[MARK858]

Change your formula to =((A6*60)/$F$2)/1440 and then format your cell as custom format hh:mm:ss (being safe) or one of the time formats.

 

[pjmorris]

I think Mark's formula reduces to this: =$A6/(F$4*24)

This simply returns the proportion of the day (which is how time is understood in Excel) that it takes to cover the distance, in other words 'distance' divided by 'distance travelled in one day'. Also, note that the absolute cell references above will make for easy formula copying: If your distances are in column A and the speeds in row 4, insert the formula in cell A6 and then copy across and down as far as necessary.

Hope this helps

 

[MARK858]

I think Mark's formula reduces to this: =$A6/(F$4*24)

It does, I was just concerned that the OP wouldn't get what the formula was doing.
Hopefully with your explanation that won't be an issue

 

[dpbarry]

Change your formula to =((A6*60)/$F$2)/1440 and then format your cell as custom format hh:mm:ss (being safe) or one of the time formats.

Guys.

Fantastic. All works like a charm. Is this calculation in effect converting the calculated time value to a decimal figure which is then formatted as a time.

Hope I worded that correctly

Declan

 

[pjmorris]

the simple answer is yes, but!

Your distance/speed equation simply provides the proportion of an hour (assuming you calculate speed as distance/hour) that is required to travel that distance. Excel considers dates and times to be a decimal number where days are the integer portion and the decimal part of a day is concerted to hours, minutes and seconds. Thus to convert your calculated time to something excel understands it must be converted to the decimal part of a day. My formula (and Mike's) effectively converts your speed from 'distance/hour' to 'distance/day'.

Hope this helps (its either helpful or as clear as mud!).

Regards

 

[MARK858]

Is this calculation in effect converting the calculated time value to a decimal figure which is then formatted as a time.

Yes.
As Peter stated time in excel is a is a fraction of a day, there are 1440 minutes in a day so if you want a normal number converted to minutes you divide it by 1440, if you wanted seconds then you would divide it by 86400 and for hours by 24.
Then obviously format it for appearance.