Hello! I feel like I am sooo close to figuring out this formula or conditional formatting but just can't quite get there. Its simple... per a collective agreement, any of employees that take their breaks "late" or don't take them at all are entitled to pay at 1.5x for that break. I get an excel sheet everyday that shows me their start time, when they took first break, second break and third break. Breaks should be taken as follows:
Break 1 - start break between 2 and 4 hours from their start time (so if they start at 1100 break one must start between 1300 an 1500hrs
Break 2 - start betwee hour 4 and 6 from start time
Break 3- start between hour and 8 from start time
**all calculated on shift start time - so even if break 1 was late, breaks 2 3might be OK
surely there must be way to highlight the cell if break start is not within these ranges?? I managed to figure out if break starts more than 2,4 or 6 hours but not in a range.
help?!?!?
<colgroup><col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 5412;">
<col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;" span="4">
<tbody>
</tbody>
Break 1 - start break between 2 and 4 hours from their start time (so if they start at 1100 break one must start between 1300 an 1500hrs
Break 2 - start betwee hour 4 and 6 from start time
Break 3- start between hour and 8 from start time
**all calculated on shift start time - so even if break 1 was late, breaks 2 3might be OK
surely there must be way to highlight the cell if break start is not within these ranges?? I managed to figure out if break starts more than 2,4 or 6 hours but not in a range.
help?!?!?
Name | Start | break1 | break 2 | break 3 |
Doe, John | 11:30 | 14:40 | 17:45 | 19:00 |
Doe, John | 06:30 | 08:55 | 11:30 | 13:15 |
Doe, John | 13:15 | 16:00 | 18:45 | 22:40 |
Doe, John | 13:15 | 16:00 | 18:45 | 22:40 |
Doe, John | 06:00 | 10:15 | 11:40 | 15:30 |
Doe, John | 12:30 | 15:20 | ||
Doe, John | 06:00 | 08:00 | 10:20 | 12:40 |
Doe, John | 14:30 | 17:00 | 19:20 | 22:05 |