Hot Date

[mlv]

I have a column of dates (assume A1:A45) and I would like to highlight the
first date in the column (reading top to bottom) that is equal to, or
nearest to a specified date (in Cell B1), but NOT less than the specified
date.

The dates will all be from the same month and will be (should be!) in
chronological order, reading top to bottom. The same date may appear more
than once in the column. Some dates will not appear at all.

For example:

A1 02/04/2008
A2 03/04/2008
A3 05/04/2008
A4 05/04/2008
A5 10/04/2008
A6 13/04/2008
A7 13/04/2008
A8 17/04/2008
A9 26/04/2008
etc.

If the specified date is 05/04/2008, Cell A3 would be highlighted.

If the specified date is 11/04/2008, Cell A6 would be highlighted.

If the specified date is 18/04/2008, Cell A9 would be highlighted.

If the specified date is 27/04/2008, and there are no further dates entered
beyond A9, then no cells would be highlighted.

Is this achievable without getting too complicated?

Am I looking at Conditional Formatting and a helper column?

TIA

 

[RagDyer]

No helper column needed.

Select A1 to A45, where the focus of the selection is A1 (colored white).

Then, in Conditional Formatting, use "Formula Is", and enter this formula:

=A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1),0))

Click on "Format", and choose whatever you like to highlight the dates.

BTW - if you have duplicates, this will highlight *all* the dates that meet
the criteria.

 

[mlv]

No helper column needed.

Select A1 to A45, where the focus of the selection is A1 (colored white).

Then, in Conditional Formatting, use "Formula Is", and enter this formula:

=A1=INDEX($A$1:$A$45,MATCH(TRUE,($A$1:$A$45>=$B$1),0))

Click on "Format", and choose whatever you like to highlight the dates.

BTW - if you have duplicates, this will highlight *all* the dates that
meet the criteria.

Thanks RD, the formula works just fine.

 

[RagDyer]

You're welcome, and thank you for the feed-back.

 

[daddylonglegs]

You could also use this formula

=A1=MIN(IF(A$1:A$45>=B$1,A$1:A$45))