# Format cell in Colum B if date in Colunm A is over 90 days

#### ILikeJam

##### New Member
Hi all!

Long-time lurker on this forum - I've pretty much always been able to find an answer to a problem as someone else has asked before but can't quite find a solution to this one that works for me.

I have a series of dates in column A (representing when an assessment was carried out). In column B there are dates for 3 month reviews. In column C there are dates for 6 month reviews.

What I would like to happen is that the date is entered in A1 and when 90 days pass B1 turns red, and when 180 days pass C1 turns red. When a date is entered in either B1 or C1 I would like it to return to white.

I can get the conditional formatting to work on A1 itself, but I can't get the format to work for B1 and C1 based on the value in A1.

In A1 I have used: Cell value is less than =NOW()-90 to format A1 red when the date is over 90 days ago which works fine.
In B1 I have tried various versions of: Formula is =\$A1=NOW()-90 but I just can't get it to work. I've had quotation marks, dollar signs and perenthises in just aout every possible combination but can't get one that works. I feel like it should be so simple and I'm missing something obvious!

Can anyone tell me if what i'm trying to do is possible or not and how to do it?

Edit - apologies for atrocious spelling in thread title!

Last edited:

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi ILikeJam,

- If the date in A1 is over 90 days old and there is no date in B1 - colour B1 red
- If the date in A1 is over 180 days old and there is no date in C1 - colour C1 red
- If there is a date entered in B1 - remove red colouring from B1
- If there is a date entered in C1 - remove red colouring from C1

Based on the above assumptions, the following Conditional Formatting should work:

In B1:
Choose "Format only cells that contain"; then choose Cell Value greater than 0 (Choose Format as Fill "No Colour")

In C1:
Choose "Format only cells that contain"; then choose Cell Value greater than 0 (Choose Format as Fill "No Colour")

In B1:
Choose: "Use a formula to determine which cells to format":
Code:
``=TODAY()-90>A1``

(Choose Format as Fill "Red")

In C1:
Choose: "Use a formula to determine which cells to format":
Code:
``=TODAY()-180>A1``

(Choose Format as Fill "Red")

Make sure that the order of the Conditional Formatting Rules is correct (Home > Conditional Formatting > Manage Rules)

They should be in the order I have listed them above.

This works for me - good luck,

Andy

AndyPandy

You sir, are a fine person indeed!

Thank you very much for that - works perfectly.
Cyber-pint to you!

And may I say you have a rather splendid name also!

Glad it all worked - marvellous news.

It's about time I was able to assist someone else instead of just receiving help all the time!

Cheers.</SPAN></SPAN>

Hi Guys, This is an old threat but hopefully you guys are still active.

I'm trying to adapt this to work for my purposes but am struggling.

I don't think i need conditional formatting, but I need to set the value of "B" depending on "A".

"B" has a drop down list with selectable inputs.

"A" has a date.

I want the text in B to say "Cold" (which is one of the selectable options) if the date in "A" is over 90 days old, unless it is overridden by a manual selection.

is this possible?

Replies
5
Views
147
Replies
1
Views
302
Replies
8
Views
374
Replies
3
Views
85
Replies
7
Views
203

1,196,073
Messages
6,013,267
Members
441,758
Latest member
Abren

### 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.

### Which adblocker are you using?

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

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