Thread: Calculating Flying hours allowing for GMT or BST

Hi
My worksheet (part of a workbook) records the amount of time spent flying,
relevant columns are shown below, items in () are how the cells are currently
formatted

B date (ddd dd-mmm-yyyy)
F take off time (hh:mm)
H landing time (hh:mm)
J BST or GMT difference +/- as a positive, minus not allowed (hh:mm)
K Flying hours after adjusting for J (dd:hh:mm) must be able to total this
column

Often the landing time goes into the next day, for which currently no date
is shown

I have tried all sorts of formulas, and are unable tofind a universal
formula to fit all options, and are now totally confused

Help would be greatly appreciated

Thanks Joco

Joco,

I would use the GMT/BST offset to be input in decimal hours, and then use

=H2-F2+(F2>H2)+J2/24

in K2

When adding, ensure the results cell is formatted as [h]:mm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joco" <Joco@discussions.microsoft.com> wrote in message
news:6F2198D2-F41E-4B2E-BF76-402259309E35@microsoft.com...
> Hi
> My worksheet (part of a workbook) records the amount of time spent flying,
> relevant columns are shown below, items in () are how the cells are
currently
> formatted
>
> B date (ddd dd-mmm-yyyy)
> F take off time (hh:mm)
> H landing time (hh:mm)
> J BST or GMT difference +/- as a positive, minus not allowed (hh:mm)
> K Flying hours after adjusting for J (dd:hh:mm) must be able to total this
> column
>
> Often the landing time goes into the next day, for which currently no date
> is shown
>
> I have tried all sorts of formulas, and are unable tofind a universal
> formula to fit all options, and are now totally confused
>
> Help would be greatly appreciated
>
> Thanks Joco

Bob Hi

Thanks for your reply, it's so simple when you know how.

In order to make your formula do what I want I have change your +J2/24 to
-J2/24
There is one cell where the answer to the formula is XXXXX this has been
caused be cell lands up with a negative time which is not permissable.

The cause of the error is because the take of time (F) and the landing time
(H) are identical ie 09:40 and 09:40 due to a refueling stop. If you know a
fix for this great, if not dont worry I have reduced the second time (H) to
09:39 and it cures the problem

I only have only mentioned the above so you are aware of it.

Once again many thanks for the great formula.

Regards Joco

"Bob Phillips" wrote:

> Joco,
>
> I would use the GMT/BST offset to be input in decimal hours, and then use
>
> =H2-F2+(F2>H2)+J2/24
>
> in K2
>
> When adding, ensure the results cell is formatted as [h]:mm
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Joco" <Joco@discussions.microsoft.com> wrote in message
> news:6F2198D2-F41E-4B2E-BF76-402259309E35@microsoft.com...
> > Hi
> > My worksheet (part of a workbook) records the amount of time spent flying,
> > relevant columns are shown below, items in () are how the cells are
> currently
> > formatted
> >
> > B date (ddd dd-mmm-yyyy)
> > F take off time (hh:mm)
> > H landing time (hh:mm)
> > J BST or GMT difference +/- as a positive, minus not allowed (hh:mm)
> > K Flying hours after adjusting for J (dd:hh:mm) must be able to total this
> > column
> >
> > Often the landing time goes into the next day, for which currently no date
> > is shown
> >
> > I have tried all sorts of formulas, and are unable tofind a universal
> > formula to fit all options, and are now totally confused
> >
> > Help would be greatly appreciated
> >
> > Thanks Joco
>
>
>

Try

=H2-F2+(F2>H2-J2/24)-J2/24

Just change the formula to

=H2-F2+(F2>=H2)-J2/24


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Joco" <Joco@discussions.microsoft.com> wrote in message
news:E027C5AD-93EE-4FAD-BAEE-6E36C0A091E7@microsoft.com...
> Bob Hi
>
> Thanks for your reply, it's so simple when you know how.
>
> In order to make your formula do what I want I have change your +J2/24 to
> -J2/24
> There is one cell where the answer to the formula is XXXXX this has been
> caused be cell lands up with a negative time which is not permissable.
>
> The cause of the error is because the take of time (F) and the landing
time
> (H) are identical ie 09:40 and 09:40 due to a refueling stop. If you know
a
> fix for this great, if not dont worry I have reduced the second time (H)
to
> 09:39 and it cures the problem
>
> I only have only mentioned the above so you are aware of it.
>
> Once again many thanks for the great formula.
>
> Regards Joco
>