Excel 2007 : change cell colour when number of days have past for a given date

**deano9512** · 11-30-2010, 11:18 AM

Hello,

i would like a cell containing a date to change colour once either 5, 10 or 30 days have passed;

for example;

Cell A1 30/11/10 5 days passed change to green
Cell A2 30/11/1010 days passed turn to blue
Cell A3 30/11/10 30 days passed turn to red.

is there a way to change using any of the formulas or some other function, i have tried conditional formatting but this does not work and i'm at a loss. what i am expecting is as each day that passes the cell counts down the days before changing colour.

i hope that makes sense.

any help would be greatly apprceated.

Thank you

**JBeaucaire** · 11-30-2010, 11:23 AM

Conditional formatting is the way to go. The formulas would be:

Condition1: Formula Is: =TODAY() - A1 >= 5
Pattern: Green

Condition2: Formula Is: =TODAY() - A1 >= 10
Pattern: Blue

Condition1: Formula Is: =TODAY() - A1 >= 30
Pattern: Red

**deano9512** · 12-01-2010, 10:31 AM

Hi Jerry,

Many thanks for responding.

I'm clearly not grasping something as i can not get it to work.

you referred to condition 1,2 & 3 - do i enter this as one formula in a cell and it will change colour once the set number of days have passed?

or

are they individual formulas that i need to enter in individual cells?

do i use the conditional formatting on the Home Tab?

i have tried inputting =TODAY() - A1 >= 5
not sure where i make this change - Pattern: Green

I keep getting a circular ref and not really sure what I’m doing wrong.

please forgive my ignorance and the barrage of questions for what may be a simple problem. I confess to being an excel idiot - however i am trying to improve on this.

Hope it all makes sense, once again many thanks for your help so far.

Deano

**JBeaucaire** · 12-01-2010, 12:28 PM

You need to read up on how to use Conditional Formatting. This isn't something that you put IN a cell like a regular formula, CF is something that is put ON the cell.

In Excel 2003 I open the CF window by Format > Conditional Formatting. You'll have to find it in Excel 2007. Press F1 and check the help files on it.

**deano9512** · 12-02-2010, 10:17 AM

Hi Jerry,

many thanks for for you patients and advice.

Deano

**JBeaucaire** · 12-02-2010, 10:35 AM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**MartinDL** · 11-08-2012, 06:13 PM

Hello All,

I really enjoy all the help I can get when it comes to excel. I am currently building a spreadsheet that will track shipments and need to have it do some conditional formatting by checking the current date and then the data in another cell(O3) for a "YES" and if Yes is not present in that cell then to change the color of that row to RED. But only if the date in cell "K3" is 2 days or more from today's date AND the "YES" is not in cell "O3" will it turn the row RED.

Thank you for any help you can provide.

Martin