Conditinal Formatting Hours of Service

Copper Contributor

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 end of the previous day shift.

 

 Ie Driver a works shift 23 Monday this shift ends at 2:00A M on Tuesday morning, if I type in Shift 1 on Tuesday it starts at 4:33AM; therefore there is less than 8 hours off between the end and start times. I need the Tuesday cell to highlight. I have used lookup functions to populate the start and end times for the shifts but cannot figure out how to conditional format the table using data from another table.

 

 Any help would be appreciated

3 Replies

@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.

@EmjBCPWT 

 

You'd help others help you if you could be a bit more explicit or descriptive here. What you have is a relatively complex spreadsheet (it may not appear so to you because you created it and therefore KNOW personally all the relationships between tabs and tables and cells).

 

So if you could point to the cell(s) in question rather than just assuming it's obvious, it would save a lot of time.

 

That said, I am noticing that although the "Run Times" table has times noted, with I think only two exceptions, there are no dates associated with those times. I do believe the calculation for the "less than 8 hours" condition would be a lot more reliable if the cells in question contained both date and time, even if only the time were to be displayed. And that might well solve your problem.
"

@mtarler Thank you so much!! I was trying to use an IF formula and just could not get it to work! You are a life saver!