Conditional formatting applied to a different cell

[ddahle1970]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I use an Excel spreadsheet at work to track and prioritize my workload.

The entries on the sheet are first sorted by date received and then by RMA number (columns D and A respectively in my spreadsheet).

I want to apply conditional formatting such that if the RMA is at least 60 days old, I want the RMA number is highlighted in blue, and if over 100 days old, then the RMA number needs to be highlighted in red.

What is the proper structure for the conditional formatting formula? I know the TODAY() function will also need to be included...

 

[John McGhie]

The two formulas you need are:

=(TODAY()-A1) > 100

=(TODAY()-A1) > 60

The longest date must be first, because the first conditional format to come
true is the only one that will fire.

Get the first cell right and copy it, then select all the others and
Edit>Paste Special>Formats to copy the formulas to all the other cells.

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
I use an Excel spreadsheet at work to track and prioritize my workload.

The entries on the sheet are first sorted by date received and then by RMA
number (columns D and A respectively in my spreadsheet).

I want to apply conditional formatting such that if the RMA is at least 60
days old, I want the RMA number is highlighted in blue, and if over 100 days
old, then the RMA number needs to be highlighted in red.

What is the proper structure for the conditional formatting formula? I know
the TODAY() function will also need to be included...

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410
+61 4 1209 1410, mailto:[email protected]

 

[ddahle1970]

It didn't work. :-/

 

[CyberTaz]

Seems to work fine here -- as long as you aren't using John's formulas
literally As I understand it your DATES are in column D, not column A,
so if, for example, they begin in D10 your Conditional Formatting formulas
for that cell should read as:

1st Condition - Formula Is: =(Today()-D10)>=100
2nd Condition - Formula Is: =(Today()-D10)>=60

Note that I also changed the comparison operator to >= because you wrote "at
least" 100 or 60 days in your previous message.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[ddahle1970]

I got it working - I placed the formatting info on column A, referencing column D. I also ended up doing >59 and >99 for the conditions.