Output result in hours and minutes

[HAH]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi all.

I'm working on a flight plan in Excel at the moment and I would like calculate how long it takes to fly a given distance at a given ground speed. Now these numbers are just numbers. For instance to fly a distance of 5 at a ground speed of 100 the formula is "=(ground speed/60)*distance". However, when I use the time format hh:mm for the outcome I get the result that it should take 08:00 (8 hours); should be 8 minutes (00:08). How do I get Excel to display it correctly?

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi all.

I'm working on a flight plan in Excel at the moment and I would like calculate
how long it takes to fly a given distance at a given ground speed. Now these
numbers are just numbers. For instance to fly a distance of 5 at a ground
speed of 100 the formula is "=(ground speed/60)*distance". However, when I use
the time format hh:mm for the outcome I get the result that it should take
08:00 (8 hours); should be 8 minutes (00:08). How do I get Excel to display it
correctly?

Divide the result by 24.

 

[HAH]

Dividing the result gives me 08:20 and not the desired 00:08.

I just found elsewhere on the internet that I should divide the result with 1400. I then found out that dividing by 1418 gives the most precise result when working with more numbers added to a given time (for instance the time of lift off). However, it's still not 100% correct and this shows when I start adding up in order to calculate an arrival time.

For now the most precise way to do this is to calculate the minutes and enter them leg for leg in the flight plan. It's more time consuming though.

 

[Laroche J]

Dividing the result gives me 08:20 and not the desired 00:08.

I just found elsewhere on the internet that I should divide the result with
1400. I then found out that dividing by 1418 gives the most precise result
when working with more numbers added to a given time (for instance the time of
lift off). However, it's still not 100% correct and this shows when I start
adding up in order to calculate an arrival time.

For now the most precise way to do this is to calculate the minutes and enter
them leg for leg in the flight plan. It's more time consuming though.

Obviously (is it?), time = distance / speed (and not speed * distance as you
wrote) is only valid when speed is constant.

During acceleration (take-off) and deceleration (landing) the relation is
time = square_root ( 2 * distance / acceleration), or time = speed change /
acceleration, in both instances only if acceleration is constant. In the
first case the equation is correct only if speed begins or ends at 0.

When speed is constant, think of distance as the area of a rectangle whose
base is time and height is speed, and during acceleration, think of distance
as the area of a rectangle triangle whose base is again time and height is
again speed. Put together, all three phases of flight create a trapeze.
Follow the sides of the trapeze: speed starts at 0, rises to maximum, stays
there for a while, then goes back down to 0. So yes, you have calculate each
phase of the flight separately.

Flying 5 (miles?) at 100 (miles per hour?) should take 3 minutes, not 8. To
display it as 00:03, use the cell format mm:ss, and use distance / speed /
1440.

1440 is 24 * 60, meaning 24 hours per day and 60 seconds per minute. It is
required to do this division because 3 means 3 days for Excel, but since
your result is already in hours you have to skip another division by 60 (for
60 minutes per hour). Simple math. As a teacher once told us: use the units
in your formulas and you'll never make a mistake.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[Laroche J]

Obviously (is it?), time = distance / speed (and not speed * distance as you
wrote) is only valid when speed is constant.

During acceleration (take-off) and deceleration (landing) the relation is
time = square_root ( 2 * distance / acceleration), or time = speed change /
acceleration, in both instances only if acceleration is constant. In the
first case the equation is correct only if speed begins or ends at 0.

When speed is constant, think of distance as the area of a rectangle whose
base is time and height is speed, and during acceleration, think of distance
as the area of a rectangle triangle whose base is again time and height is
again speed. Put together, all three phases of flight create a trapeze.
Follow the sides of the trapeze: speed starts at 0, rises to maximum, stays
there for a while, then goes back down to 0. So yes, you have calculate each
phase of the flight separately.

Flying 5 (miles?) at 100 (miles per hour?) should take 3 minutes, not 8. To
display it as 00:03, use the cell format mm:ss, and use distance / speed /
1440.

1440 is 24 * 60, meaning 24 hours per day and 60 seconds per minute. It is
required to do this division because 3 means 3 days for Excel, but since
your result is already in hours you have to skip another division by 60 (for
60 minutes per hour). Simple math. As a teacher once told us: use the units
in your formulas and you'll never make a mistake.

EDIT:
You should actually divide by 24 only, not 1440, and use the cell format
hh:mm:ss. Therefore, 3 minutes will show as 00:03:00.

4 miles at 100 miles per hour will show as 00:02:24. If you don't want to
see the seconds, use cell format hh:mm.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[HAH]

It really isn't that complex. This fight plan is only meant to keep track of arrival times at various report points during a VFR flight. Take off and landing is therefore irrelevant in this case. The distance is in nautical miles and the speed is the ground speed in knots. However, it makes no difference really. At a speed of 100 it takes 8,33 minutes to travel a distance of five; at a speed of 100 you travel a distance of 1,66 per minute.

The speed is pretty constant as you've already calculated the wind's influence on the plane, when you figure out your ground speed.

The question was only how to get Excel to convert a number (which in this case is a result in minutes) to hh:mm.

After experimenting a bit I see now that when I use the hh:mm format, the calculated total time i wrong. It's only correct if I use the hh:mm:ss format and divide the result with 1440 as you say.

So with the example where the speed is 100 and the distance is 5 the formula is now "=((100/60)*5)/1440" and then choose the hh:mm:ss format in the cell that displays the result.

Thank you guys for your swift help )

 

[Laroche J]

It really isn't that complex. This fight plan is only meant to keep track of
arrival times at various report points during a VFR flight. Take off and
landing is therefore irrelevant in this case. The distance is in nautical
miles and the speed is the ground speed in knots. However, it makes no
difference really. At a speed of 100 it takes 8,33 minutes to travel a
distance of five; at a speed of 100 you travel a distance of 1,66 per minute.

The speed is pretty constant as you've already calculated the wind's influence
on the plane, when you figure out your ground speed.

The question was only how to get Excel to convert a number (which in this case
is a result in minutes) to hh:mm.

After experimenting a bit I see now that when I use the hh:mm format, the
calculated total time i wrong. It's only correct if I use the hh:mm:ss format
and divide the result with 1440 as you say.

So with the example where the speed is 100 and the distance is 5 the formula
is now "=((100/60)*5)/1440" and then choose the hh:mm:ss format in the cell
that displays the result.

Thank you guys for your swift help )

HAH,

Sorry to insist, but your formula is wrong.

Distance is in nautical miles and speed in knots, which is actually nautical
miles per hour. If you use your formula you end up with:
(nm / h) / (min / h) * nm / (min / d) = nm^2 / min^2, hardly a measure of
time.

As I said, time = distance / speed, i.e in units nm / (nm / h) = h, a true
unit of time. Throw in the factor 24 for Excel to display properly and you
should have in your example =5/100/24.

Think about it another way. To cover 5 nautical miles at 100 knots takes
1/20 of an hour, which is 3 minutes, not 8. To cover 100 nautical miles at
100 knots takes an hour, not 2.46 hours (100/60*100/1440) you'd get with
your formula (actually 2.46 square nautical miles per square minute at
that!). With your formula, the faster you go the longer the flight lasts,
which is illogical.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

 

[HAH]

You're absolutely right. I worked myself blind on this one. I treated the distance as minutes. My formula is now "=(distance/(speed/60))/1440" and the time is now 3 minutes.

Thanks a lot, Laroche )

 

[Laroche J]

You're absolutely right. I worked myself blind on this one. I treated the
distance as minutes. My formula is now "=(distance/(speed/60))/1440" and the
time is now 3 minutes.

Thanks a lot, Laroche )

You're welcome. I just feel safer now flying in areas your software will
cover ;-))

By the way, you realize that (distance/(speed/60))/1440 is exactly the same
than distance/speed/24, just more complicated and less intuitive?

And to close the "units" debate, I had even made a mistake this morning
reporting the result of your previous equation. It would have been nm^2 * d
/ min^2. Forgot the "day" part. Just in case somebody would scrutinize my
work and demolish my demonstration. Phew! Have a nice day.

JL
Mac OS X 10.4.11, Office v.X 10.1.9