Calculating Flying hours allowing for GMT or BST

[Joco]

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 Phillips]

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]

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

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)

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 Phillips]

Just change the formula to

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


--
HTH

Bob Phillips

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

 

[daddylonglegs]

Try

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