Solved Change a cells color in Excel by a date.

October 3, 2013 at 14:34:34
Specs: Windows 7
Hello, I am in an Army Airborne Unit and I am the Jump Log custodian. It is a requirement that soldiers Jump every 90 days. More important the Jump Master must perform their duties every 180 days. I would like to build a spreadsheet that tracks this I would like to keep it simple so Green for when they are good and gradually turn red when they are out of currency. Here is an example of what I have, Name, Date of the last time duties where performed and the date that they must perform the duties to stay current. I put a formula in Colum C that adds 180 days to the adjacent date in Colum B. I would like to see the soldiers name turn red and I know there is a way for the cell to turn colors gradually using the three color option.

Jump Master	Performed Duties On:	     Must Perform Duties By:
         Corley	         4-Sep-13	                    3-Mar-14
         Lindsey	         4-Apr-13	                    1-Oct-13
         Adkisson	         1-Oct-13	                   30-Mar-14
         Evans	         22-Feb-13	                   21-Aug-13

message edited by Steven720


See More: Change a cells color in Excel by a date.

Report •


✔ Best Answer
October 3, 2013 at 20:34:26
I would like to see the soldiers name turn red

This is for Excel 2007, see if this works for you:

With your data like:

       A                   B                       C
1) Jump Master    Performed Duties On:   Must Perform Duties By:
2) Corley               04-Sep-13                 03-Mar-14
3) Lindsey              04-Apr-13                 01-Oct-13
4) Adkisson             01-Oct-13                 30-Mar-14
5) Evans                22-Feb-13                 21-Aug-13


There are Four separate formulas,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

10 Days = Red
30 Days = Orange
60 Days = Yellow
90 Days = Green

First Formula:

1) Select your cell or Range of Cells, A2 - A5 (Names)
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: =C2-TODAY()<90

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

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
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: =C2-TODAY()<60

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


Third Formula:

1) Select your cell or Range of Cells, should be the same as above
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: =C2-TODAY()<30

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

Fourth Formula:

1) Select your cell or Range of Cells, should be the same as above
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: =C2-TODAY()<10

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

MIKE

http://www.skeptic.com/



#1
October 3, 2013 at 17:10:43
I think I understand what your looking to do.

Do you want the whole Row to turn colors?

What time frames do you want to utilize, for example:
10 days before Must Perform turn Red
15 days before Must Perform turn Orange
30 days before Must Perform turn Yellow

or some other combination of colors and days?

When would you want it Green?

MIKE

http://www.skeptic.com/


Report •

#2
October 3, 2013 at 18:14:51
Thank you for answering so quickly. Yes those time frames are close.. 1-30 would be green, 30-60 Yellow and 60-90 Red. I am assuming this solution would work for the ones requiring 180 days in between requirements as well by just change the # of days variable?

Report •

#3
October 3, 2013 at 19:04:29
1-30 would be green, 30-60 Yellow and 60-90 Red.

Please understand that when dealing with Dates you must be very specific about what you mean and which dates your referring to.

With that in mind, do you want the cells to turn Green between 1 and 30 days Before
the Must Perform date?
Or, do you mean you want the cells to turn Green between 1 and 30 days After
the Performed Duties On date?

Also, do you want the Row to turn color, or just a single cell, or something in between?


MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 3, 2013 at 20:34:26
✔ Best Answer
I would like to see the soldiers name turn red

This is for Excel 2007, see if this works for you:

With your data like:

       A                   B                       C
1) Jump Master    Performed Duties On:   Must Perform Duties By:
2) Corley               04-Sep-13                 03-Mar-14
3) Lindsey              04-Apr-13                 01-Oct-13
4) Adkisson             01-Oct-13                 30-Mar-14
5) Evans                22-Feb-13                 21-Aug-13


There are Four separate formulas,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

10 Days = Red
30 Days = Orange
60 Days = Yellow
90 Days = Green

First Formula:

1) Select your cell or Range of Cells, A2 - A5 (Names)
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: =C2-TODAY()<90

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

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
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: =C2-TODAY()<60

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


Third Formula:

1) Select your cell or Range of Cells, should be the same as above
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: =C2-TODAY()<30

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

Fourth Formula:

1) Select your cell or Range of Cells, should be the same as above
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: =C2-TODAY()<10

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

MIKE

http://www.skeptic.com/


Report •

#5
October 3, 2013 at 21:06:26
Amazing Brother!! Thanks it worked like a champ!!

Report •


Ask Question