Conditional Formatting: Highlight Cell that contains soonest Day

[Pete]

I have a column/row with dates and would like to have the soonest Date highlighted, but there is no function which a formula could be passed to.

What I'd like to have is something like this:
MATCH(TODAY(), A1:A100, -1)

but unfortunately "The lookup_value argument can be a value (number, text, or logical value)..." and not a function.
I also tried this:
MATCH(Z1, A1:A100, -1) with Z1=TODAY() what throws the "Value Not Available Error"

Does anybody have an idea how to work this out?

 

[Claus Busch]

Hi Pete,

Am Sun, 15 Apr 2012 11:02:24 -0700 (PDT) schrieb Pete:

I have a column/row with dates and would like to have the soonest Date highlighted, but there is no function which a formula could be passed to.

What I'd like to have is something like this:
MATCH(TODAY(), A1:A100, -1)

but unfortunately "The lookup_value argument can be a value (number, text, or logical value)..." and not a function.
I also tried this:
MATCH(Z1, A1:A100, -1) with Z1=TODAY() what throws the "Value Not Available Error"

try:
=A1=INDEX(A:A,MATCH(LARGE(A:A,COUNTIF(A:A,">="&TODAY())),A:A,0))


Regards
Claus Busch

 

[Pete]

Hi Pete,

Am Sun, 15 Apr 2012 11:02:24 -0700 (PDT) schrieb Pete:


try:
=A1=INDEX(A:A,MATCH(LARGE(A:A,COUNTIF(A:A,">="&TODAY())),A:A,0))


Regards
Claus Busch

Thanks.
That works fine if I place the formula somewhere in the sheet and add conditional formatting referencing to this cell, but it doesn't work in a conditional formatting formula itself:

Conditional formatting Rule:
Formula: =INDEX(A4:A51,MATCH(LARGE(A4:A51,COUNTIF(A4:A51,">="&TODAY())),A4:A51,0))
Applies to =$A:$A

In this case the whole column is highlighted

Any ideas?

 

[Claus Busch]

Am Sun, 15 Apr 2012 11:51:27 -0700 (PDT) schrieb Pete:

Thanks.
That works fine if I place the formula somewhere in the sheet and add conditional formatting referencing to this cell, but it doesn't work in a conditional formatting formula itself:

Conditional formatting Rule:
Formula: =INDEX(A4:A51,MATCH(LARGE(A4:A51,COUNTIF(A4:A51,">="&TODAY())),A4:A51,0))
Applies to =$A:$A

In this case the whole column is highlighted

Any ideas?

Regards
Claus Busch

 

[Claus Busch]

Hi Pete,

Am Sun, 15 Apr 2012 11:51:27 -0700 (PDT) schrieb Pete:

That works fine if I place the formula somewhere in the sheet and add conditional formatting referencing to this cell, but it doesn't work in a conditional formatting formula itself:

I've tested the formula in CF.
Select A1:A100 => CF and then the formula in CF:
=A1=INDEX(A:A,MATCH(LARGE(A:A,COUNTIF(A:A,">"&TODAY())),A:A,0))


Regards
Claus Busch

 

[Claus Busch]

Hi Pete,

Am Sun, 15 Apr 2012 11:51:27 -0700 (PDT) schrieb Pete:

Conditional formatting Rule:
Formula: =INDEX(A4:A51,MATCH(LARGE(A4:A51,COUNTIF(A4:A51,">="&TODAY())),A4:A51,0))
Applies to =$A:$A

select A4:A51 => CF and the formula:
=A4=INDEX(A:A,MATCH(LARGE(A:A,COUNTIF(A:A,">"&TODAY())),A:A,0))


Regards
Claus Busch

 

[Pete]

Hi,
thanks a lot. That has worked for me.

Hi Pete,

Am Sun, 15 Apr 2012 11:51:27 -0700 (PDT) schrieb Pete:


select A4:A51 => CF and the formula:
=A4=INDEX(A:A,MATCH(LARGE(A:A,COUNTIF(A:A,">"&TODAY())),A:A,0))


Regards
Claus Busch