Spreadsheet help with formulas

[vonwasden]

I'm building a spreadsheet to track CDL driver hours. I have a couple of formulas or conditional formatting (I'm not sure which one) that I need to figure out.

1) I have three cells per day, one for driving hours logged, one for hours off duty before noon and one for hours off duty after noon. They are stacked into two rows, the hours logged and merged and centered over the other two. I would like to be able to add up these three cells to ensure that they equal 24 hours. If they don't equal 24 hours I would like the cells to change color to notify me that there is a mistake.

2) Underneath these three cells is a running total cell that adds up the current day plus the preceding 7 days to make sure it doesn't total more than 70 hours. I want this cell to also turn red if the total over those 8 days exceeds 65 hours. I have figured out how to do that part. The problem is that if the drivers have 34 hours off consecutively, then they can reset their hours to zero for their total. I need a formula that will recognize 34 hours off time and reset the total to 0. I think it will need to add the three off duty hours adjoining each other to do that.

Here is a copy of the general layout of the cells (sorry I can't copy a better section of the spreadsheet here) The first two lines and the month and date. The 3rd line is the on-duty driving hours. The 4th line are the am & pm off-duty hours. The 5th line is the running total line. I hope this make sense.

January
1 2 3 4 5 6 7 8 9
12 12 12 12 12 12 12
6 6 6 6 6 6 6 6 6 6 6 6 6 6
12 24 36 48 60 72 84 84 72


Any help on this would be greatly appreciated, thanks!