Shift schedule conditional formatting
 

I am trying to make a worksheet that lays out the shift plans for all 3 shifts. We have several processes and we know how long each step takes in the process. So I built a work sheet that calculates the time of each step based on the start time. I formatted the column into military time. I want to do conditional formatting to color the cell based on what shift the step would fall on.

1st =and(celltime(0,6,0),cell<(14,0,0))
2nd =and(celltime(0,14,0),cell<(22,0,0))
3rd =and(celltime(0,22,0),cell<(24,0,0))
3rd =and(celltime(0,0,0),cell<(6,0,0))

Everything works fine except I can not get the midnight-6am conditional to format to work. I have even tried 1am-6am with no luck. I am sure it is some silly error, but I can't seem to figure it out.

Essentially I want
1st 6am-2pm = yellow
2nd 2:01pm-10pm = orange
3rd 10:01pm-6am = blue
And then for no color to appear at all when the cell is blank

Any help would be appreciated. Thanks

Shift schedule conditional formatting
 

Hi John,

Am Fri, 5 Jun 2015 08:48:48 -0700 (PDT) schrieb John Petty:

Essentially I want
1st 6am-2pm = yellow
2nd 2:01pm-10pm = orange
3rd 10:01pm-6am = blue
And then for no color to appear at all when the cell is blank

have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for workbook "Time"
But what if working time is e.g. 11:00-16:00
or 2:00-4:00?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional