Hi

I have dates in a column in this format :

16/01/2010
16/02/2010
16/03/2010
16/04/2010
16/05/2010
16/06/2010


I'm trying to set the conditional formatting so that the nearest date to

today is in bold.

Can someone help with this?


Grateful for any advice.



Best Wishes

Hi there!

Sure, I can help you with that. Here's how you can highlight the date nearest to TODAY() using conditional formatting in Excel:

1. Select the range of cells containing your dates.
2. Go to the Home tab and click on Conditional Formatting.
3. Select New Rule.
4. In the New Formatting Rule dialog box, select "Use a formula to determine which cells to format."
5. In the formula box, enter the following formula:
   Formula:

   =ABS(A1-TODAY())=MIN(ABS($A$1:$A$6-TODAY())) 

   (Note: Replace A1:A6 with the range of cells containing your dates)
6. Click on the Format button and choose the formatting you want to apply to the nearest date.
7. Click OK to close the Format Cells dialog box.
8. Click OK to close the New Formatting Rule dialog box.

Now, the date nearest to TODAY() will be highlighted with the formatting you chose. You can also change the formatting or adjust the range of cells as needed.

Define nearest.

If these were your dates:

15/3/2010
17/3/2010

And today was 16/3/2010

Which is the nearest date?

--
Biff
Microsoft Excel MVP


"Colin Hayes" wrote in message
...


Hi

I have dates in a column in this format :

16/01/2010
16/02/2010
16/03/2010
16/04/2010
16/05/2010
16/06/2010


I'm trying to set the conditional formatting so that the nearest date to

today is in bold.

Can someone help with this?


Grateful for any advice.



Best Wishes

In article , T. Valko
writes
Define nearest.

If these were your dates:

15/3/2010
17/3/2010

And today was 16/3/2010

Which is the nearest date?


HI

Thanks for getting back.

All the dates are a month apart in my example. So I guess could Today ()
versus the given date could be set to plus or minus 16.

That would probably do the trick , but I can't think my way through the
formula to enter into the conditional formatting interface.

Thanks for your help.

Assuming your data is sort in ascending order

Conditional Formatting:

=$A1=INDEX($A$1:$A$6,MATCH(TODAY(),$A$1:$A$6))



"Colin Hayes" wrote:



Hi

I have dates in a column in this format :

16/01/2010
16/02/2010
16/03/2010
16/04/2010
16/05/2010
16/06/2010


I'm trying to set the conditional formatting so that the nearest date to

today is in bold.

Can someone help with this?


Grateful for any advice.



Best Wishes
.

OK, but you didn't answer my question.

If these were your dates:

15/3/2010
17/3/2010

And today was 16/3/2010

Which is the nearest date?

Both of the dates are an equal difference from the target date. So which is
the nearest date?

--
Biff
Microsoft Excel MVP


"Colin Hayes" wrote in message
...
In article , T. Valko
writes
Define nearest.

If these were your dates:

15/3/2010
17/3/2010

And today was 16/3/2010

Which is the nearest date?


HI

Thanks for getting back.

All the dates are a month apart in my example. So I guess could Today ()
versus the given date could be set to plus or minus 16.

That would probably do the trick , but I can't think my way through the
formula to enter into the conditional formatting interface.

Thanks for your help.

See attached. Not much for real formulas were needed. Used Conditional
formatting in Excel 2007.

A1 has "=Today()"

Then I used Conditional formatting Highlight Cells Rules Between...



"Colin Hayes" wrote in message
...


Hi

I have dates in a column in this format :

16/01/2010
16/02/2010
16/03/2010
16/04/2010
16/05/2010
16/06/2010


I'm trying to set the conditional formatting so that the nearest date to

today is in bold.

Can someone help with this?


Grateful for any advice.



Best Wishes

This is almost perfect for me. Would you show me a version that gives the closest date that is today or later, instead of potentially in the past? I'm using this in a Holidays spreadsheet, and today is closer to Halloween than Veteran's Day, so the above formula shows Halloween, but since Halloween is already in the past, I'd like to show the next closest holiday from the current date (including today's date, if it's a holiday). E.g., if today's date was Halloween, I would want to highlight that instead of Veteran's Day.

My dates are in ascending order. Thanks in advance for any help.