Highlight cells that are closest to today

[Habari11]

Hi all,


This should be a piece of cake for Excel guru. I am trying to find a way to automatically highlight cells that are closeset to today. Dates were entered across colums and need to find a way to quickly find the date that is closest to today. Please help!

 

[SirJB7]

Hi, Habari11!

Would you please elaborate a bit more? Or upload a sample file (check 2nd. green sticky post at this forums main page for guidelines)?

Without knowing the structure of your worksheet it's very difficult to answer properly.

Regards!

 

[Luke M]

Use a CF formula similar to this:

=ABS(B2-TODAY())=MIN(ABS($B$2:$G$2-TODAY()))


Where B2 is the current cell you are formatting, and B2:G2 is the range of dates.

 

[SirJB7]

@SirJB7

Hi, myself!

So long...

That's what I thought and wrote! Who in the hell read that dates were across the "worksheet" instead of across "columns"?

Regards!

PS: How is Sandy behaving?

 

[Habari11]

Here is more detail. On each row, there is a name of a customer (A column) followed by 10 dates in each column (B to K). Each date represents the scheduled visits and is separted by 30 days. All the rows in the sheet follows the same format but dates are different by customer. I would like the Excel to automatically highlight a cell in each row that is closest today's date. This automatic format will apply to all the dates (B-K columns). I hope this is clearer.

 

[Luke M]

Select cells B2:K2. With B2 active, CF formula is:

=ABS(B2-TODAY())=MIN(ABS($B2:$K2-TODAY()))


Copy formatting down as needed.


@SirJB7

Sandy's making a mess of things, causing all sorts of shut downs and evacuations. Luckily, my location is safe, but I have had to reschedule my travel plans. =/

 

[SirJB7]

@Luke M

Hi!

I'm following the news about her and it's very impressive. Wish you remain safe, thing which I discount: your CASFFML issue protects you from Sandy's effects, no shared files no storm forecasts.

Regards and good luck!