# 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.

October 3, 2013 at 20:34:26
 I would like to see the soldiers name turn redThis 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 = Red30 Days = Orange60 Days = Yellow90 Days = GreenFirst 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 OK10) Click OKSecond 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 OK10) Click OKThird 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 OK10) Click OKFourth 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 OK10) Click OKMIKEhttp://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 Red15 days before Must Perform turn Orange30 days before Must Perform turn Yellowor some other combination of colors and days?When would you want it Green?MIKEhttp://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 Beforethe 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? MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
October 3, 2013 at 20:34:26
 I would like to see the soldiers name turn redThis 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 = Red30 Days = Orange60 Days = Yellow90 Days = GreenFirst 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 OK10) Click OKSecond 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 OK10) Click OKThird 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 OK10) Click OKFourth 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 OK10) Click OKMIKEhttp://www.skeptic.com/