automatically update colors based on dates

**laurend** · 11-13-2009, 02:23 PM

So I'm making a spreadsheet based on dates. I want the cell to be filled with different colors based on when the date is 1 month away, 3 months away, and 6 months away. And then when it becomes 1 month away, 3 months away, or 6 months away, I want the color to automatically change. I'm not sure this is even possible, but any help would be much appreciated.

**Paul** · 11-13-2009, 02:30 PM

Hi Laurend, welcome to the forum.

You can do this pretty simply using Conditional Formatting. Try doing a search for conditional formatting dates and you'll get a wealth of information.

Hope that helps!

**Palmetto** · 11-13-2009, 02:32 PM

To automatically "color" a cell use Conditional Formatting.
See this link for help on the topic. Contextures - Conditional Formatting

when it becomes 1 month away, 3 months away, or 6 months away, I want the color to automatically change

You'll have t provide more details, such as what is the comparison date by which to judge if a date in the cell is 1-month away?

**mohitspamz** · 11-13-2009, 02:44 PM

Hey!
You can do this using conditional formatting.
Select the rows where u want this to be done then go to:
Format>Conditional Formatting
Choose >formula is> =Row-Today()<30.... then choose wotever colour you want the date be in.. then click add... and so on...

There are many threads and links u'll find on conditional formatting...

Hope this gives u a start...

Regards
Mohit

**laurend** · 11-17-2009, 11:12 AM

The comparison date would be today. So I want the colors to automatically change when the item is 30 days away from being due, 60 days away from being due, and 90 days away. The date in the columns is the due date, which is what I want colored. All the help I'm getting is for an older version of Excel. I have the newest version.

**Palmetto** · 11-17-2009, 01:00 PM

All the help I'm getting is for an older version of Excel. I have the newest version.

None of the comments, thus far, are version specific. The suggested solution below will work will all versions (at least 2000-2007).

To use conditional formatting to color cells based on values being within a date range you will need to use formulas like these.

CF formula #1: AND(A1>=TODAY()-90,A1<=TODAY()-60)
CF Formula #2: AND(A1>=TODAY()-60,A1<=TODAY()-30)
CF Formula #3: AND(A1>=TODAY()-30,A1<=TODAY())

See the attached.