Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating numbers of hours worked in pay divisions | Excel Discussion (Misc queries) | |||
Calculating working hours | Excel Worksheet Functions | |||
calculating hours | Excel Worksheet Functions | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |