Could somebody help me with formula in G20, is working as intended mostly but can make it not calculate when cell in D is empty.
TIA
TIA
| Fatigue 1.xlsx | |||
|---|---|---|---|
| C | |||
| 14 | 8/04/2024 20:33 | ||
Phase 1 | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C14 | C14 | =+NOW() |
| Fatigue 1.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | |||
| 20 | 5/04/2024 6:00 | y | 5/04/2024 17:00 | 16:30 | 5/04/2024 23:00 | |||
| 21 | 5/04/2024 7:00 | y | 5/04/2024 17:00 | 15:30 | 6/04/2024 0:00 | |||
| 22 | 5/04/2024 8:00 | y | 5/04/2024 17:00 | 14:30 | 6/04/2024 1:00 | |||
| 23 | 5/04/2024 6:30 | y | 5/04/2024 17:00 | 16:00 | 5/04/2024 23:30 | |||
| 24 | 5/04/2024 5:30 | y | 5/04/2024 17:00 | 17:00 | 5/04/2024 22:30 | |||
| 25 | 5/04/2024 7:00 | y | 5/04/2024 17:00 | 15:30 | 6/04/2024 0:00 | |||
| 26 | 5/04/2024 6:30 | y | 5/04/2024 17:00 | 16:00 | 5/04/2024 23:30 | |||
| 27 | 5/04/2024 7:00 | y | 5/04/2024 17:00 | 15:30 | 6/04/2024 0:00 | |||
| 28 | 5/04/2024 7:00 | n | 5/04/2024 17:00 | 15:30 | 6/04/2024 0:00 | |||
| 29 | 5/04/2024 7:00 | y | 5/04/2024 17:00 | 15:30 | 6/04/2024 0:00 | |||
| 30 | 5/04/2024 6:00 | 5/04/2024 17:00 | 86:33 | 5 | 6/04/2024 4:00 | |||
| 31 | 1089380:33 | |||||||
| 32 | 1089380:33 | |||||||
| 33 | 1089380:33 | |||||||
| 34 | 1089380:33 | |||||||
Phase 1 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G20:G34 | G20 | =IF(K20="",$C$14-D20,K20-D20) |
| I20:I30 | I20 | =IFERROR(IF(D20="","",D20+17/24)+H20/24,"") |
| I31:I34 | I31 | =IFERROR(IF(D31="","",D31+17/24)-H31/24,"") |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| I18:I50 | Cell | contains a blank value | text | NO |
| I18:I50 | Expression | =NOW()-$D18>=TIME(17,0,0) | text | YES |
| I18:I50 | Expression | =NOW()-$D18>=TIME(14,0,0) | text | YES |
| I18:I50 | Expression | =NOW()-$D18<=TIME(14,0,0) | text | YES |
| E18:E34,E39:E76,E94:E126 | Cell Value | contains "N" | text | NO |
