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

ILikeJam

New Member
Joined
Apr 11, 2013
Messages
2
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! :confused:

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

Thanks very much in advance! :cool:

Edit - apologies for atrocious spelling in thread title!
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi ILikeJam,

From reading through your post, I think I understand; the following are the "rules" for your spreadsheet:

- 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
 
Upvote 0
AndyPandy

You sir, are a fine person indeed!

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

And may I say you have a rather splendid name also!
 
Upvote 0
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>
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top