Forum Discussion
Conditinal Formatting Hours of Service
Hi All, I have attached my excel workbook that I am looking for assistance on; I need to have excel highlight/ color the cell when a shift # is typed in that starts less than 8 hours from the en...
EmjBCPWT i assume it is the table on 'PT template' in rows 21:36 that you need this formatting. Here is the custom formatting -> custom formula I applied to the range B23:O36 (the range is important because the formula is all based off the upper left corner cell of that applied range):
=AND(B$22="Start",(B23+1-A23)<1/3)
see attached.
Note: this is also based on the way you defined your end times that carry over a day actually being +1 day. i.e. your example of a shift ending at 2:00 AM the next day is actually stored as the next day and not just the time of 2:00 AM. So if you change or add additional shifts you must maintain that convention.
