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.

A
1. A shift
2. B shift
3. C shift

message edited by Holyoblation


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

Reply ↓  Report •

✔ Best Answer
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 like
just 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 sequence
is not 3, 6, 9 days but 4, 7, 10 days
So your first shift runs until the 4th day at 6:00 AM
the second shift runs until the 7th day at 6:00 AM
and the last day of the sequence occurring on day 10 at 6:00 AM
That is the reason for the extra Rule

You did not specify which version of Excel your using
so 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 Formatting
3) 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")<=3

6) Click on the Format button
7) Select the Fill Tab
8) Select color Yellow
9) Click OK
10) Click OK

Second Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 4th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Yellow
9) Click OK
10) Click OK

Third Rule for A Shift

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")<=6

6) Click on the Format button
7) Select the Fill Tab
8) Select color Red
9) Click OK
10) Click OK

Fourth Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 7th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")=7,NOW()-TODAY()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Red
9) Click OK
10) Click OK

Fifth Rule for A Shift

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")<=9

6) Click on the Format button
7) Select the Fill Tab
8) Select color Green
9) Click OK
10) Click OK

Sixth Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 10th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")=10,NOW()-TODAY()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Green
9) Click OK
10) Click OK

------------------------------------------------------------------------------

First Rule for B Shift, cell A2:

=DATEDIF($X$1,TODAY(),"D")<=3
Select color Red

Second Rule for B Shift

=AND(DATEDIF($X$1,TODAY(),"D")=4,NOW()-TODAY()<TIME(6,0,0))
Select color Red

Third Rule for B Shift

=DATEDIF($X$1,TODAY(),"D")<=6
Select color Green

Fourth Rule for B Shfit

=AND(DATEDIF($X$1,TODAY(),"D")=7,NOW()-TODAY()<TIME(6,0,0))
Select color Green

Fifth Rule for B Shift

=DATEDIF($X$1,TODAY(),"D")<=9
Select color Yellow

Sixth Rule for B Shift

=AND(DATEDIF($X$1,TODAY(),"D")=10,NOW()-TODAY()<TIME(6,0,0))
Select color Yellow

------------------------------------------------------------------------------

First Rule for C Shift, cell A3: ( Green, Yellow, Red)

=DATEDIF($X$1,TODAY(),"D")<=3
Select color Green

Second Rule for C Shift

=AND(DATEDIF($X$1,TODAY(),"D")=4,NOW()-TODAY()<TIME(6,0,0))
Select color Green

Third Rule for C Shift

=DATEDIF($X$1,TODAY(),"D")<=6
Select color Yellow

Fourth Rule for C Shfit

=AND(DATEDIF($X$1,TODAY(),"D")=7,NOW()-TODAY()<TIME(6,0,0))
Select color Yellow

Fifth Rule for C Shift

=DATEDIF($X$1,TODAY(),"D")<=9
Select color Red

Sixth Rule for C Shift

=AND(DATEDIF($X$1,TODAY(),"D")=10,NOW()-TODAY()<TIME(6,0,0))
Select color Red

------------------------------------------------------------------------------

As you can see, the Rules for all 3 Shifts are the same,
the only thing that changes is the Color sequence.

Shift A is Yellow, Red, Green
Shift B is Red, Green, Yellow
Shift C is Green, Yellow, Red

This can also be modified if you wish.

The last piece of the puzzle is a bit of Macro Code, supplied by DerbyDad03,
which checks to see if we have reached day 10, and if it is after 6:00 AM,
at which time it simply changes the date in cell X1 to Todays() date.

The code should be stored in the ThisWorkbook module and will run
each time the workbook is opened.

Private Sub Workbook_Open()
'Is the current date greater than or equal to X1 + 10 days and is it after 6AM?
   If Date >= 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 cycle
of colors forever, but as in all things computer related, your mileage may vary.

MIKE

http://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

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

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

Reply ↓  Report •

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

Reply ↓  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 Macro
to accomplish what you want.
Unfortunately, my macro skills are just above nil, but hopefully someone with
more ability will offer a suggestion.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

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

Reply ↓  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.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

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

Reply ↓  Report •

#8
October 8, 2017 at 06:21:25
✔ Best Answer
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 like
just 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 sequence
is not 3, 6, 9 days but 4, 7, 10 days
So your first shift runs until the 4th day at 6:00 AM
the second shift runs until the 7th day at 6:00 AM
and the last day of the sequence occurring on day 10 at 6:00 AM
That is the reason for the extra Rule

You did not specify which version of Excel your using
so 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 Formatting
3) 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")<=3

6) Click on the Format button
7) Select the Fill Tab
8) Select color Yellow
9) Click OK
10) Click OK

Second Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 4th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Yellow
9) Click OK
10) Click OK

Third Rule for A Shift

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")<=6

6) Click on the Format button
7) Select the Fill Tab
8) Select color Red
9) Click OK
10) Click OK

Fourth Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 7th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")=7,NOW()-TODAY()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Red
9) Click OK
10) Click OK

Fifth Rule for A Shift

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")<=9

6) Click on the Format button
7) Select the Fill Tab
8) Select color Green
9) Click OK
10) Click OK

Sixth Rule for A Shift
This will check to see if the Time is before 6:00 AM on the 10th
day, if it is, the color stays the same.

1) Select your cell ie: A1 (A Shift)
2) On the ribbon click Conditional Formatting
3) 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")=10,NOW()-TODAY()<TIME(6,0,0))

6) Click on the Format button
7) Select the Fill Tab
8) Select color Green
9) Click OK
10) Click OK

------------------------------------------------------------------------------

First Rule for B Shift, cell A2:

=DATEDIF($X$1,TODAY(),"D")<=3
Select color Red

Second Rule for B Shift

=AND(DATEDIF($X$1,TODAY(),"D")=4,NOW()-TODAY()<TIME(6,0,0))
Select color Red

Third Rule for B Shift

=DATEDIF($X$1,TODAY(),"D")<=6
Select color Green

Fourth Rule for B Shfit

=AND(DATEDIF($X$1,TODAY(),"D")=7,NOW()-TODAY()<TIME(6,0,0))
Select color Green

Fifth Rule for B Shift

=DATEDIF($X$1,TODAY(),"D")<=9
Select color Yellow

Sixth Rule for B Shift

=AND(DATEDIF($X$1,TODAY(),"D")=10,NOW()-TODAY()<TIME(6,0,0))
Select color Yellow

------------------------------------------------------------------------------

First Rule for C Shift, cell A3: ( Green, Yellow, Red)

=DATEDIF($X$1,TODAY(),"D")<=3
Select color Green

Second Rule for C Shift

=AND(DATEDIF($X$1,TODAY(),"D")=4,NOW()-TODAY()<TIME(6,0,0))
Select color Green

Third Rule for C Shift

=DATEDIF($X$1,TODAY(),"D")<=6
Select color Yellow

Fourth Rule for C Shfit

=AND(DATEDIF($X$1,TODAY(),"D")=7,NOW()-TODAY()<TIME(6,0,0))
Select color Yellow

Fifth Rule for C Shift

=DATEDIF($X$1,TODAY(),"D")<=9
Select color Red

Sixth Rule for C Shift

=AND(DATEDIF($X$1,TODAY(),"D")=10,NOW()-TODAY()<TIME(6,0,0))
Select color Red

------------------------------------------------------------------------------

As you can see, the Rules for all 3 Shifts are the same,
the only thing that changes is the Color sequence.

Shift A is Yellow, Red, Green
Shift B is Red, Green, Yellow
Shift C is Green, Yellow, Red

This can also be modified if you wish.

The last piece of the puzzle is a bit of Macro Code, supplied by DerbyDad03,
which checks to see if we have reached day 10, and if it is after 6:00 AM,
at which time it simply changes the date in cell X1 to Todays() date.

The code should be stored in the ThisWorkbook module and will run
each time the workbook is opened.

Private Sub Workbook_Open()
'Is the current date greater than or equal to X1 + 10 days and is it after 6AM?
   If Date >= 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 cycle
of colors forever, but as in all things computer related, your mileage may vary.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#9
October 8, 2017 at 06:31:49
Wow. Does your head hurt? lol
That looks great.

Reply ↓  Report •

#10
October 8, 2017 at 10:42:14
It is far from a perfect solution, but thanks for the compliments.

One caveat that come to mind, is you MUST open the workbook on the 10th Day after 6:00am to get the correct updated Date.

If you skip opening the workbook on the 10th day
and do not open the workbook until the 11th day,
then the date will be wrong and all your shift colors will be off.

This could be a problem if the 10th day falls on a weekend, Holiday, your day off, or if you go on vacation.

There may be a Macro solution to this problem but unfortunately, I'm not sure.

Let us know how it works.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

Ask Question