# Conditional Formatting question.

#### HerrUther

##### New Member
Hello all,

I'm trying to create a spreadsheet to track assessments on personnel.

I've created a list of columns that use the formula =IF(E46="","",DATE(YEAR(E46),MONTH(E46)+12,DAY(E46))) to auto calculate the next date for assessment this one is for the 12 monthly reviews. I've also added conditional formatting that changes the date to orange 60 days before expiry and red once expired.

These dates continue to run and what I'm trying to achieve is a some kind of conditional formatting or something else of something that changes the expired date colour to 'No formatting' once the next date field is completed.

Sorry for the lack of pictures...

I'm using excel 2010 and I'm a very basic user so please keep it simple

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Jerry Sullivan

##### MrExcel MVP
Hi and Welcome to MrExcel,

You can add a criteria to each Conditional Formatting (CF) Rule that tests if the "next date field is completed".

It's hard to suggest a specific formula without knowing what your existing CF Rules are, and to which cells those apply, and what it means to complete a date field.

As an example, if your <60 days =Orange Rule for Cell E48 was:
=TODAY()>E48-60

and you are testing if F48 has a value or is blank to indicate the next date field is completed, you could modify the CF Rule to....
=AND(TODAY()>E48-60,LEN(F48)>0)

#### HerrUther

##### New Member
Hi Jerry,

My CF is 'Format only cells that contain' 'cell value' 'less than' '=NOW()' for red and '=NOW()+60' for Orange. This is applied to multiple cells in multiple rows =\$D\$3:\$D\$70, I think this is my issue... I'm I asking too much of the CF maybe?

I would like the red field to be cancelled by the next date in the next cell once completed, don't know if it's possible.

I tired =AND(NOW(),LEN(\$E\$3:\$E\$70)>0) but all it did was make all cells red even without data...

#### Jerry Sullivan

##### MrExcel MVP
Instead of using ''Format only cells that contain', use Rule Type:
'Use a formula to determine which cells to format'

Remove all your existing CF rules in the sheet, select cell D3, then make a rule for Red:

Format values where this formula is true:
=AND(D3< NOW(),LEN(E3)=0)

Applies to: \$D\$3:\$D\$70

#### HerrUther

##### New Member
Thanks for your support and understanding of my limited exposure to excel...

So if I create another rule for Orange I'd just use:

Format values where this formula is true:
=AND(D3< NOW()+60,LEN(E3)=0) Applies to: \$D\$3:\$D\$70??

and then ensure that the is red and then orange in rules manager??

Genius... I knew there was a reason to love excel

Replies
29
Views
652
Replies
3
Views
128
Replies
3
Views
160
Replies
3
Views
218
Replies
2
Views
226

1,191,118
Messages
5,984,749
Members
439,907
Latest member
Kayfabe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back