excelproblems
New member
- Joined
- Nov 5, 2014
- Messages
- 4
- Reaction score
- 0
- Points
- 0
I need a formula that reads:
Hours worked between 24:00 (Midnight) and 30:00 (6am) are special overtime (O7).
Examples.
09:00 - 12:00 = No Overtime.
07:00 - 18:00 = No Overtime.
24:00 - 29:00 = Overtime. Hours are between Midnight and 6am.
24:00 - 10:00 = Overtime. Hours are between Midnight and 6am. 4 Hours at Regular Time (6:00 - 10:00)
23:00 - 27:00 = Overtime. Hours are between Midnight and 6am. 1 Hour at Regular Time (23:00 - 24:00)
My cells are laid out as follows:
Formulas for current cells:
Regular Time: Up to 8 Hours =SUM(H7-G7)*24-I7-O7-M7-Q7
Overtime: 8 - 12 Hours =IF(J7>8,IF(J7>8,(J7-8)))-Q7
SPECIAL TIME: Hours are between Midnight and 6am
Double time: Over 12 Hours =IF(J7>12,IF(J7>12,(J7-12)))-T7 Note: T7 = 0 Always.
Hours worked between 24:00 (Midnight) and 30:00 (6am) are special overtime (O7).
Examples.
09:00 - 12:00 = No Overtime.
07:00 - 18:00 = No Overtime.
24:00 - 29:00 = Overtime. Hours are between Midnight and 6am.
24:00 - 10:00 = Overtime. Hours are between Midnight and 6am. 4 Hours at Regular Time (6:00 - 10:00)
23:00 - 27:00 = Overtime. Hours are between Midnight and 6am. 1 Hour at Regular Time (23:00 - 24:00)
My cells are laid out as follows:
| START TIME (G7) | END TIME (H7) | REGULAR TIME (K7) | OVERTIME (M7) | SPECIAL TIME(O7) | DOUBLE TIME (Q7) |
Formulas for current cells:
Regular Time: Up to 8 Hours =SUM(H7-G7)*24-I7-O7-M7-Q7
Overtime: 8 - 12 Hours =IF(J7>8,IF(J7>8,(J7-8)))-Q7
SPECIAL TIME: Hours are between Midnight and 6am
Double time: Over 12 Hours =IF(J7>12,IF(J7>12,(J7-12)))-T7 Note: T7 = 0 Always.
