Seven lucky logs
New Member
- Joined
- Nov 26, 2012
- Messages
- 4
Hi Gents,
I have the following spreadsheet
Column A continues down to "23:55" in cell A298. Column B continues down to B298 and holds various values between "11:00" and "14:00" depending on time in column A.
The top range denoted with an "A" is linked to the third range again with an "A" and this shows that the formulae are working. The second set of linked ranges show a negative value when, by my manual working, it should be zero. The problem is only when the result should be zero; if the result should be over, it is and is shown correctly. Likewise for results that should be under. Am I rounding too much?
I'm designing (hopefully) a tool to calculate working hours for aircrew by entering flight times in GMT and adjusting the time zone to suit.
Many thanks for any ideas
Tim
I have the following spreadsheet
Excel 2012 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | -0.20833 | 1.50 | |||||||||
2 | Start | End | A | -0.20833 | |||||||
3 | 08:00 | 17:15 | -5 | ||||||||
5 | |||||||||||
6 | Start | End | B | ||||||||
7 | 08:00 | 17:30 | -5 | ||||||||
8 | |||||||||||
9 | A | ||||||||||
10 | Start | Start | End | Status | Buffer / Over limits | ||||||
11 | 00:00 | 11:00 | 01:30 | 03:00 | 12:15 | Within limits by | 00:15 | ||||
12 | 00:05 | 11:00 | |||||||||
13 | 00:10 | 11:00 | B | ||||||||
14 | 00:15 | 11:00 | Start | Start | End | Status | Buffer / Over limits | ||||
15 | 00:20 | 11:00 | 01:30 | 03:00 | 12:30 | Within limits by | ######################## | ||||
16 | 00:25 | 11:00 | |||||||||
17 | 00:30 | 11:00 | |||||||||
18 | 00:35 | 11:00 | |||||||||
19 | 00:40 | 11:00 | |||||||||
20 | 00:45 | 11:00 | |||||||||
21 | 00:50 | 11:00 | |||||||||
22 | 00:55 | 11:00 | |||||||||
23 | 01:00 | 11:00 | |||||||||
24 | 01:05 | 11:00 | |||||||||
25 | 01:10 | 11:00 | |||||||||
26 | 01:15 | 11:00 | |||||||||
27 | 01:20 | 11:00 | |||||||||
28 | 01:25 | 11:00 | |||||||||
29 | 01:30 | 11:00 | |||||||||
Time different from GMT |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H1 | =F3/24 | |
H2 | =F3/24 | |
C11 | =D11-$I$1/24 | |
C15 | =D15-$I$1/24 | |
D11 | =IF(D3="","",D3+$H$1+(D3>E3)) | |
D15 | =IF(D7="","",D7+$H$2+(D7>E7)) | |
E11 | =IF(E3="","",E3+$H$1+(D3>E3)) | |
E15 | =IF(E7="","",E7+$H$2+(D7>E7)) | |
F11 | {=IF(C11="","",IF(INDEX($B$11:$B$298,MATCH(ROUND(C11,10),ROUND($A$11:$A$298,10)+(C11>E11),0))>=((E11-C11)+(C11>E11)),"Within limits by","Exceeds Max FDP by"))} | |
F15 | {=IF(C15="","",IF(INDEX($B$11:$B$298,MATCH(ROUND(C15,10),ROUND($A$11:$A$298,10)+(C15>E15),0))>=((E15-C15)+(C15>E15)),"Within limits by","Exceeds Max FDP by"))} | |
G11 | {=IF(F11="Exceeds Max FDP by",((E11-C11)+(C11>E11))-INDEX($B$11:$B$298,MATCH(ROUND(C11,10),ROUND($A$11:$A$298,10)+(C11>E11),0)),IF(F11="Within limits by",INDEX($B$11:$B$298,MATCH(ROUND(C11,10),ROUND($A$11:A298,10)+(C11>E11),0))-(E11-C11)+(C11>E11),""))} | |
G15 | {=IF(F15="Exceeds Max FDP by",((E15-C15)+(C15>E15))-INDEX($B$11:$B$298,MATCH(ROUND(C15,10),ROUND($A$11:$A$298,10)+(C15>E15),0)),IF(F15="Within limits by",INDEX($B$11:$B$298,MATCH(ROUND(C15,10),ROUND($A$11:A302,10)+(C15>E15),0))-(E15-C15)+(C15>E15),""))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Column A continues down to "23:55" in cell A298. Column B continues down to B298 and holds various values between "11:00" and "14:00" depending on time in column A.
The top range denoted with an "A" is linked to the third range again with an "A" and this shows that the formulae are working. The second set of linked ranges show a negative value when, by my manual working, it should be zero. The problem is only when the result should be zero; if the result should be over, it is and is shown correctly. Likewise for results that should be under. Am I rounding too much?
I'm designing (hopefully) a tool to calculate working hours for aircrew by entering flight times in GMT and adjusting the time zone to suit.
Many thanks for any ideas
Tim