Color a cell based off of a date and number of days until task is delinquent

Occasional Contributor

My first time posting. Been using Excel for years at the basic level but not a need to produce a spreadsheet that utilizes the customization tools.

Capture1.PNG

So can't seem to get this to work using a formula, just can't get it to work. What I need some help with is how to make G2 in the above to turn a color based off the date input into F2 and the number of days from Today.

 

- Say today is NOV 1, Suspend Date (F2) is Dec 15, that's 45 days so I want to show it as yellow for upcoming task. Anything over 60 days would be blue. If the task isn't indicated as completed in L 2 then G2 goes red because the math becomes a negative number. 

 

Am I making this too difficult on myself, or is there and easier way to get this to work. 

 

Thanks, 

 

7 Replies

Hi @williamhill43 

 

You need to use conditional format, create three rules in as the same order bellow:

Select the cells in column G2:G10, from home tab --> styles-->conditional formatting --> Manage rules

From the windows click on new rule --> Use a formula to determine which cells to format --> write the first formula in the format values where this formula is true box, --> click on Formatrules.png button and pick up the blue color, --> OK

 

=F2- TODAY()>60  

=F2- TODAY()>0

=L2<>"Completed"

 

Regarding the third condition, it is not clear what you want 

Great, Thank you for the assistance.

I'm not much worried about the L column because it's a drop down list and I select the proper condition.

Thank you again for the input.
Ok, so one more question. If I were to select Complete from the L2 dropdown is there a way to color G2 green and leave the formula in the G2 cell?

@williamhill43 

You can create a new rule for G2:G10 the same way as before, with the formula

 

=L2="Completed"

 

and green as fill color.

That worked, thank you. Is there a way to change the text color green also so that the past due number of days doesn't show when the cell turns green.

@williamhill43 

Yes - select the range, then click Conditional Formatting > Manage Rules...

Select the rule for green, and click Edit.

Click Format...

Activate the Font tab, and select the same font color that you used in the Fill tab.

Then OK your way out.

Hans, you are awesome thank you for the assistance.