Hello,
I need some help to create a formula that will alert me if the working hours professionals reported is compliant or not.
Explanation:
I'm building a working hours report spreadsheet to be used by HR. We are currently running on 3 different shifts:
1st Shift: 08:00am - 12:00pm / 13:00 - 16:30 (1h lunch time that will not count) = Total 8 hours
2nd Shift: 16:00 - 20:00 / 21:00 - 00:00 (1h lunch time that will not count) = Total 7 hours
3rd Shift: 00:00 - 03:00/ 04:00 - 08:00 (1h lunch time that will not count) = Total 7 hours
So, the rules I need to be in place is:
- If the total amount of Hours EXCEEDS 16 min or MORE (1st shift = 8h16min / 2nd and 3rd shift = 07:16min) on Column Q, the cells would return "H.E" in RED,
- If the total amount of Hours are LESS or more (1st shift = 07h44min / 2nd and 3rd shift = 06h44min) on Column Q, the cells would return "DESCONTAR" in RED.
- If any of those rules matches, of course the hours reported are acceptable, the cell would return as "OK" in Green.
The shift hours that I'm using as reference are in Cells C,D,E,F 7. I'm trying to link it to Column Q, but if I set Conditional Formatting to pain the cells in red, it won't accept 3 different rules and I don't know what else to do.
Can someone please help?
Thanks!
Bookmarks