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.

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.

On Sun, 13 Apr 2008 12:37:00 -0700, Teethless mama
<(E-Mail Removed)> 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.

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.

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.

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" <(E-Mail Removed)> wrote in message
news:AC29CC8E-4EFC-4FDA-83E4-(E-Mail Removed)...
>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.

"Fred Smith" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:e6yyNJ$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:AC29CC8E-4EFC-4FDA-83E4-(E-Mail Removed)...
>>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.
>

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
news:%23$9wjf$(E-Mail Removed)...
> "Fred Smith" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:e6yyNJ$(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:AC29CC8E-4EFC-4FDA-83E4-(E-Mail Removed)...
>>>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.
>>
>

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
<(E-Mail Removed)> 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.

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" <(E-Mail Removed)>
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
>news:%23$9wjf$(E-Mail Removed)...
>> "Fred Smith" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>> news:e6yyNJ$(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:AC29CC8E-4EFC-4FDA-83E4-(E-Mail Removed)...
>>>>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.
>>>
>>