"If" function and Upcoming Dates

[mafioso420]

Hello, this is my first post and I could really use some help with th
problem. I'm setting up a roster for a boy scout troop. And using a
upcoming date in relation to the date in the cell. I need to display on
of three colors for that cell. Example: If Scout's med form (dat
displayed) expires in less than 30 days; fill said cell YELLOW, or i
it's past due the cell is RED, if more than 30 days away, said cell i
GREEN. All in relation to the current date when reading form.

I'm not sure if this is possible but if it is, I figured someone ou
there would know how to do it. Thank you for taking the time to rea
this

 

[zvkmpw]

I need to display one

of three colors for that cell. Example: If Scout's med form (date
displayed) expires in less than 30 days; fill said cell YELLOW, or if
it's past due the cell is RED, if more than 30 days away, said cell is
GREEN. All in relation to the current date when reading form.

Excel's "Conditional Formatting" (CF) is designed for this.

The details of how to set up the conditions vary in different versions of Excel, so check the built-in Help in your version.

Here's one way to proceed.

Start by selecting the entire column containing the dates.

Then select the command to activate CF. In Excel 2010, it's
Home > Styles > Conditional Formatting > Manage rules

You'll want to have three rules. When given the choice for each rule, "use a formula" to determine which cells to format.

For each rule, you enter a formula and you choose the formatting for that rule, such as cell fill color. Leave the whole column selected while setting the rules.

The formulas below assume column A holds the dates; if your dates are in a different column, change the formulas accordingly. The rules should end up in this order.

Rule 1 is yellow-filled cell format with formula
=AND(ISNUMBER(A1),TODAY()<=A1,TODAY()>=A1-30)

Rule 2 is red-filled cell format with formula
=AND(ISNUMBER(A1),TODAY()>A1)

Rule 3 is green-filled cell format with formula
=ISNUMBER(A1)

Hope this helps getting started.