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

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.

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

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

On Sun, 13 Apr 2008 12:37:00 -0700, Teethless mama
wrote:

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" wrote:

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.

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

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

"Hercdriver" wrote:

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.

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

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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

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

"JB Bates" wrote in message
...
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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

"Fred Smith" wrote:
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" wrote in 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

"JB Bates" wrote in message
...
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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

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

Regards,
Fred

"Joe User" <joeu2004 wrote in message
...
"Fred Smith" wrote:
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" wrote in 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

"JB Bates" wrote in message
...
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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

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
wrote:

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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

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.



On Mon, 28 Dec 2009 16:01:05 -0600, "Fred Smith"
wrote:

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

Regards,
Fred

"Joe User" <joeu2004 wrote in message
...
"Fred Smith" wrote:
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" wrote in 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

"JB Bates" wrote in message
...
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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

Updated to include mil time selections.

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

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




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

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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.

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

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.



On Mon, 28 Dec 2009 17:30:00 -0800, CellShocked
<cellshocked@thecellvalueattheendofthespreadsheet. org wrote:

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
wrote:

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?

"Teethless mama" wrote:

A2: T/O
B2: LAND

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


"Hercdriver" wrote:

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.