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

Calculating Flying hours allowing for GMT or BST
 

Joco,

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

=H2-F2+(F2H2)+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" wrote in message
...
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

Calculating Flying hours allowing for GMT or BST
 

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+(F2H2)+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" wrote in message
...
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

Calculating Flying hours allowing for GMT or BST
 

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

Calculating Flying hours allowing for GMT or BST
 

Try

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=564269