I have an existing spreadsheet that is a calendar with multiple years and conditional formatting based on which crew a person is working on. This is from a few years ago and we have started working the same schedule but we are starting on a different day- one day later to be exact. The rotation is 2 days-2 nights- 4 days off. We like to highlight the days we work and print it out to carry in our wallets for convenience when making appointments etc. I have found the formula that sets the formatting but if I change the date (advancing it one day) from what is there all the formatting disappears.

Copuld someone with great Excel skills take a look at this and make some suggestions?



"=AND(A14<>"",((((TEXT($A$11,0)+A14-1)-$D$9)+1)-(INT((((TEXT($A$11,0)+A14-1)-$D$9)+1)/8)*8))>$A$95) Conditional format day shift

=AND(A14<>"",((((TEXT($A$11,0)+A14-1)-$D$9)+1)-(INT((((TEXT($A$11,0)+A14-1)-$D$9)+1)/8)*8))>3) Conditional format night shift

=IF(Data!B20=3,"01/04/2002",IF(Data!B20=2,"01/06/2002",IF(Data!B20=1,"01/08/2002","01/02/2002"))) Start date selection {I tried changing the dates in this section to 01/05/2002-advanced one day but it will not report a date value to the below cell (A9) and the formatting is gone.}

=DATEVALUE(A9) Date value from start date selection."

I will attach spreadsheet as well. I am not the person who created the document and that person does not work here and/or is not available.

Attached Files

Shift Calendar.xls (75.5 KB, 39 views)