Excel 2010 - Conditional Format based of passed date

[remeng]

Hi All;

I am trying to figure out if a date is in the past and if so, format the cell by highlighting it. I am having problems distinguishing between blank cells and dated cells.

4 conditions:

1 - Determine if the cell is blank or not (working)
2 - Determine if the date is within the next month (working)
3 - Determine if the date is over a month (working)
4 - Determine if the date is previous to the current date (not working)

Steps taken:

1) Select the range of cells
2) Click conditional formatting rules
3) new rule
4) ?

I think it needs to be a formula but I don't know what it should be.

Thank you for the assistance,

Mike

 

[SkipVought]

Hi,

1 - Determine if the cell is blank or not (working)
2 - Determine if the date is within the next month (working)
3 - Determine if the date is over a month (working)
4 - Determine if the date is previous to the current date (not working)

Assuming that A2 is the TOP LEFT cell of the Selection Range and column A contains the date.

I can't determine from your 4 requirements what span the date should be, but here are my assumptions: any date in the previous month through any date in the next month
[tt]
=AND(NOT(ISBLANK(A2)),DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)<=A2,DATE(YEAR(TODAY()),MONTH(TODAY())+2,1)>A2)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[randy700]

The formula I use is:
Cell value between 37987 and TODAY()-1


Randy

 

[SkipVought]

randy700, where did you get 1/1/2004 in the above requirement?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[randy700]

It's just an example - what I'm currently using in a spreadsheet.
My data will never be older than 1/1/2004.
If yours could be older, use an earlier date.

Another option would be cell value less than TODAY().


Randy

 

[SkipVought]

The objective is to address the OP's question.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[randy700]

So sorry, Skip. I thought giving the OP an example to go by would help address his question and solve his problem.
I didn't realize that I'm not supposed to offer suggestions other than those YOU give - I'll refrain in the future.



Randy