# Solved Change color of cell based on rotating work schedule

September 28, 2017 at 08:38:28
Specs: Android
 I have a group of employees that work 6 days on call and 3 days off. They are divided into three shifts A, B and C. 2 shifts are on call at all times. Every 3 days at 06:00 1 shift goes off and another comes on. I would like the color of cells A1:A3 to change every 3rd day at 06:00. Green for shift that just came on, yellow for shift that is still on and red for shift that just went off. And continue on forever. A1. A shift2. B shift3. C shift message edited by Holyoblation

See More: Change color of cell based on rotating work schedule

October 8, 2017 at 06:21:25
 Here is a possible solution for your Three Color problem.First you will need to add a single Date cell to your sheet,I have used cell X1, but you can change it to any place you likejust remember to change the formula & codes.Next you will need 6 Conditional Formatting Rules for each Shift:3 rules to denote which color to use ( Red, Green or Yellow )and 3 rules to check for 6:00 am rule change.Your shifts run past midnight, so technically the sequenceis not 3, 6, 9 days but 4, 7, 10 days So your first shift runs until the 4th day at 6:00 AMthe second shift runs until the 7th day at 6:00 AMand the last day of the sequence occurring on day 10 at 6:00 AMThat is the reason for the extra RuleYou did not specify which version of Excel your usingso this is for Excel 2007.-------------------------------------------------------------------------------First Rule for A Shift, cell A1: 1) Select your cell ie: A1 (A Shift)2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(\$X\$1,TODAY(),"D")<=36) Click on the Format button7) Select the Fill Tab8) Select color Yellow9) Click OK10) Click OKSecond Rule for A ShiftThis will check to see if the Time is before 6:00 AM on the 4thday, if it is, the color stays the same.1) Select your cell ie: A1 (A Shift)2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(DATEDIF(\$X\$1,TODAY(),"D")=4,NOW()-TODAY()= DateValue(Range("X1")) + 10 And Hour(Now) > 6 Then 'If both are yes, set X1 to current date Range("X1") = Date End If End Sub ```Theoretically, once initiated, the conditional formatting should run it's cycleof colors forever, but as in all things computer related, your mileage may vary. MIKEhttp://www.skeptic.com/

#1
September 28, 2017 at 12:11:36
 re: 2 shifts are on call at all times. Every 3 days at 06:00 1 shift goes off and another goes off.That doesn't make sense. If 2 shifts go off at 6:00, then you wouldn't have 2 shifts on at all times. Do you mean that the shift schedule looks something like this?```Day A B C 1 On On Off 2 On On Off 3 On On Off 4 On Off On 5 On Off On 6 On Off On 7 Off On On 8 Off On On 9 Off On On 10 On On Off 11 On On Off 12 On On Off 13 On Off On```

Report •

#2
September 28, 2017 at 12:25:47
 Yes. Like that. I corrected my question.

Report •

#3
September 30, 2017 at 12:02:34
 Anybody got any ideas on how to make this happen?

Report •

Related Solutions

#4
September 30, 2017 at 16:12:49
 I do not believe it can be done with just a formula you will probably need a Macroto accomplish what you want.Unfortunately, my macro skills are just above nil, but hopefully someone withmore ability will offer a suggestion. MIKEhttp://www.skeptic.com/

Report •

#5
September 30, 2017 at 16:15:36
 I hear ya. Thanks

Report •

#6
September 30, 2017 at 17:51:18
 One of the challenges with this is having everything change at 6:00PM. That means that something has to be running constantly in order for the spreadsheet to know what time it is.You would either need a macro that is constantly running in a loop, always checking the time, or perhaps use the Windows Task Scheduler to initiate an event at 6:00PM on the scheduled days.I could write the VBA code but it bothers me to have it constantly running, wasting system resources, just to change some cell colors every three days. As far as using the Windows Task Scheduler or a Dos Batch program, that's beyond my expertise. However, I think that that's a better idea.

Report •

#7
October 1, 2017 at 10:47:02
 Yep. You're right. I don't really need color changing cells.

Report •

#8
October 8, 2017 at 06:21:25
 Here is a possible solution for your Three Color problem.First you will need to add a single Date cell to your sheet,I have used cell X1, but you can change it to any place you likejust remember to change the formula & codes.Next you will need 6 Conditional Formatting Rules for each Shift:3 rules to denote which color to use ( Red, Green or Yellow )and 3 rules to check for 6:00 am rule change.Your shifts run past midnight, so technically the sequenceis not 3, 6, 9 days but 4, 7, 10 days So your first shift runs until the 4th day at 6:00 AMthe second shift runs until the 7th day at 6:00 AMand the last day of the sequence occurring on day 10 at 6:00 AMThat is the reason for the extra RuleYou did not specify which version of Excel your usingso this is for Excel 2007.-------------------------------------------------------------------------------First Rule for A Shift, cell A1: 1) Select your cell ie: A1 (A Shift)2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =DATEDIF(\$X\$1,TODAY(),"D")<=36) Click on the Format button7) Select the Fill Tab8) Select color Yellow9) Click OK10) Click OKSecond Rule for A ShiftThis will check to see if the Time is before 6:00 AM on the 4thday, if it is, the color stays the same.1) Select your cell ie: A1 (A Shift)2) On the ribbon click Conditional Formatting3) Click on New Rules, it’s near the bottom of the dialog box.4) Click Use Formula to determine which cells to format.5) Enter the formula: =AND(DATEDIF(\$X\$1,TODAY(),"D")=4,NOW()-TODAY()= DateValue(Range("X1")) + 10 And Hour(Now) > 6 Then 'If both are yes, set X1 to current date Range("X1") = Date End If End Sub ```Theoretically, once initiated, the conditional formatting should run it's cycleof colors forever, but as in all things computer related, your mileage may vary. MIKEhttp://www.skeptic.com/

Report •

#9
October 8, 2017 at 06:31:49