Adding time in 24 hour format to produce hours in decimal format

[Hercdriver]

I need to add aircraft takeoff and land times in a 24 hour (military) format
to come up with flight hours in a decimal format across days (the date itself
is not important).
Example:
Takeoff 2231 to land 0138 the next morning is 3.1 hours of flight time and
would look like this:
T/O LAND Hours
2231 0138 3.1

The tenths of hours are standard flight time accounting and go like this:
0-5 min= .0, 6-11 min= .1, 12-17 min= .2, 18- 23 min= .3, 24-29 min= .4,
30-35 min= .5, 36-41 min= .6, 42-47 min= .7, 48-53 min= .8, and 54-59 min= .9.

I'll be using Excel 2002 to do this, if it can be done at all. Thanks in
advance.

 

[Teethless mama]

A2: T/O
B2: LAND

In C2: =ROUND(MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)*24,1)

 

[Lars-Åke Aspelin]

A2: T/O
B2: LAND

In C2: =ROUND(MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)*24,1)

I guess you should replace ROUND with ROUNDDOWN to make the rounding
according to the specification.

Lars-Åke

 

[Hercdriver]

Thanks! Works like a charm. You guys (gals) are great!

 

[JB Bates]

I am looking to do the same thing but we do not use the government time
calculation for our flight time.

We use

0-2 = .0
3-8 = .1
9-14 = .2
15-20 = .3
21-26 = .4
27-32 = .5
33-38 = .6
39-44 = .7
45-50 = .8
51-56 = .9
57-59 = 1.0

Any suggestions?

 

[Fred Smith]

You're making the problem a lot more difficult than it is. The conversion
table you are using simply rounds the minutes to the nearest tenth of an
hour.

To convert an Excel time in a1 to a number, use:
=a1*24
Format the number with one decimal place. It will display what you want.

If you want to discard anything after the first decimal, use:
=round(a1*24,1)

Regards,
Fred

 

[Joe User]

You're making the problem a lot more difficult than it is. [....]
If you want to discard anything after the first decimal, use:
=round(a1*24,1)

You are at a disadvantage because JB has been splitting his question among
several postings. So you are not seeing the question in the proper context,
I presume.

Although ROUND(A1*24,1) should round time the minute, ROUND((A1-A2)*24,1)
does not result in 0.2 as expected when A1 is 22:43 and A2 is 22:34, even
though their difference is displayed as 00:09 when formatted as hh:mm, and
(A1-A2)*24 is displayed as 0.15 when formatted as Number, unless we format
to 15 decimal places.

As David explains in the thread that has all the context (klunk!), the
reason ROUND "fails" is because (A1-A2)*24 is infinitesimally less than 0.15
for those particular values.

Consequently, the problem of rounding hh:mm to fractional hours exactly
according to JB's table is indeed more difficult than simply using ROUND.

And while David and others suggested tweaks to the subtraction formula to
make it work, the more general solution (e.g. if the result of the
subtraction is already a value in A3) might be:

=ROUND(TEXT(A3,"hh:mm")*24,1)

PS: In yet another thread (and context, sigh), David suggested using
MOD(A1-A2,1)*24 instead of simply (A1-A2)*24 in order to cover the case
where the time difference crosses midnight. JB neglects to explain that in
subsequent threads, leading to well-intentioned, but misleading suggestions
that the use of MOD is unnecessary.


----- original message -----

 

[Fred Smith]

Thanks for the update. People could save themselves (and responders) a lot
of time if they stuck to the same thread.

Regards,
Fred

You're making the problem a lot more difficult than it is. [....]
If you want to discard anything after the first decimal, use:
=round(a1*24,1)

You are at a disadvantage because JB has been splitting his question among
several postings. So you are not seeing the question in the proper
context, I presume.

Although ROUND(A1*24,1) should round time the minute, ROUND((A1-A2)*24,1)
does not result in 0.2 as expected when A1 is 22:43 and A2 is 22:34, even
though their difference is displayed as 00:09 when formatted as hh:mm, and
(A1-A2)*24 is displayed as 0.15 when formatted as Number, unless we format
to 15 decimal places.

As David explains in the thread that has all the context (klunk!), the
reason ROUND "fails" is because (A1-A2)*24 is infinitesimally less than
0.15 for those particular values.

Consequently, the problem of rounding hh:mm to fractional hours exactly
according to JB's table is indeed more difficult than simply using ROUND.

And while David and others suggested tweaks to the subtraction formula to
make it work, the more general solution (e.g. if the result of the
subtraction is already a value in A3) might be:

=ROUND(TEXT(A3,"hh:mm")*24,1)

PS: In yet another thread (and context, sigh), David suggested using
MOD(A1-A2,1)*24 instead of simply (A1-A2)*24 in order to cover the case
where the time difference crosses midnight. JB neglects to explain that
in subsequent threads, leading to well-intentioned, but misleading
suggestions that the use of MOD is unnecessary.


----- original message -----

You're making the problem a lot more difficult than it is. The conversion
table you are using simply rounds the minutes to the nearest tenth of an
hour.

To convert an Excel time in a1 to a number, use:
=a1*24
Format the number with one decimal place. It will display what you want.

If you want to discard anything after the first decimal, use:
=round(a1*24,1)

Regards,
Fred

 

[CellShocked]

The time cells on this sheet are exactly what you want.

The sheet even has time increment selection available.


_http://www.mediafire.com/?ioyt2wzwyn4


You can easily use the math functions, and the rest as well, if you
like.

The time gets entered in mil format, but appears as standard time
because of the drop down list. The cells that perform the math
calculations are dead on what you want, however.


\On Mon, 28 Dec 2009 10:49:01 -0800, JB Bates

 

[CellShocked]

Not all these dopes have even the first clue about Usenet, and have
less of a clue about the fact that changing the header title makes a new,
completely detached thread.

Thanks for the update. People could save themselves (and responders) a lot
of time if they stuck to the same thread.

Regards,
Fred

You're making the problem a lot more difficult than it is. [....]
If you want to discard anything after the first decimal, use:
=round(a1*24,1)

You are at a disadvantage because JB has been splitting his question among
several postings. So you are not seeing the question in the proper
context, I presume.

Although ROUND(A1*24,1) should round time the minute, ROUND((A1-A2)*24,1)
does not result in 0.2 as expected when A1 is 22:43 and A2 is 22:34, even
though their difference is displayed as 00:09 when formatted as hh:mm, and
(A1-A2)*24 is displayed as 0.15 when formatted as Number, unless we format
to 15 decimal places.

As David explains in the thread that has all the context (klunk!), the
reason ROUND "fails" is because (A1-A2)*24 is infinitesimally less than
0.15 for those particular values.

Consequently, the problem of rounding hh:mm to fractional hours exactly
according to JB's table is indeed more difficult than simply using ROUND.

And while David and others suggested tweaks to the subtraction formula to
make it work, the more general solution (e.g. if the result of the
subtraction is already a value in A3) might be:

=ROUND(TEXT(A3,"hh:mm")*24,1)

PS: In yet another thread (and context, sigh), David suggested using
MOD(A1-A2,1)*24 instead of simply (A1-A2)*24 in order to cover the case
where the time difference crosses midnight. JB neglects to explain that
in subsequent threads, leading to well-intentioned, but misleading
suggestions that the use of MOD is unnecessary.


----- original message -----

You're making the problem a lot more difficult than it is. The conversion
table you are using simply rounds the minutes to the nearest tenth of an
hour.

To convert an Excel time in a1 to a number, use:
=a1*24
Format the number with one decimal place. It will display what you want.

If you want to discard anything after the first decimal, use:
=round(a1*24,1)

Regards,
Fred

I am looking to do the same thing but we do not use the government time
calculation for our flight time.

We use

0-2 = .0
3-8 = .1
9-14 = .2
15-20 = .3
21-26 = .4
27-32 = .5
33-38 = .6
39-44 = .7
45-50 = .8
51-56 = .9
57-59 = 1.0

Any suggestions?

:

A2: T/O
B2: LAND

In C2: =ROUND(MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)*24,1)


:

I need to add aircraft takeoff and land times in a 24 hour (military)
format
to come up with flight hours in a decimal format across days (the
date itself
is not important).
Example:
Takeoff 2231 to land 0138 the next morning is 3.1 hours of flight
time and
would look like this:
T/O LAND Hours
2231 0138 3.1

The tenths of hours are standard flight time accounting and go like
this:
0-5 min= .0, 6-11 min= .1, 12-17 min= .2, 18- 23 min= .3, 24-29 min=
.4,
30-35 min= .5, 36-41 min= .6, 42-47 min= .7, 48-53 min= .8, and 54-59
min= .9.

I'll be using Excel 2002 to do this, if it can be done at all.
Thanks in
advance.

 

[CellShocked]

Updated to include mil time selections.

Just select mil in the increment selection dialog on the info page.

_http://www.mediafire.com/?o4lj4ljzymm

 

[CellShocked]

It would not be all that hard at all to modify that workbook to express
in "flights" instead of "jobs".

For flights, however, I would think that one would want a daily log,
so the sheet could be broken down into specific segments, like 2 Hrs each
or such.

Or it could be used as a yearly log for a single plane, where every
flight or even maintenance periods could easily be defined to explain
periods of non-flight, etc.