Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 17th 10, 02:40 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 465
Default Highlight date nearest to TODAY()



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

  #2   Report Post  
Old March 17th 10, 03:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Highlight date nearest to TODAY()

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



  #3   Report Post  
Old March 17th 10, 03:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 465
Default Highlight date nearest to TODAY()

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.

  #4   Report Post  
Old March 17th 10, 03:39 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 3,718
Default Highlight date nearest to TODAY()

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
.

  #5   Report Post  
Old March 17th 10, 04:03 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Highlight date nearest to TODAY()

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.





  #6   Report Post  
Old March 17th 10, 04:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: May 2009
Posts: 140
Default Highlight date nearest to TODAY()

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


  #7   Report Post  
Old November 7th 19, 11:08 PM
Junior Member
 
First recorded activity by ExcelBanter: Nov 2019
Posts: 1
Smile Almost Just What I Need

Quote:
Originally Posted by Teethless mama View Post
Assuming your data is sort in ascending order

Conditional Formatting:
=$A1=INDEX($A$1:$A$6,MATCH(TODAY(),$A$1:$A$6))
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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highlight date nearest to TODAY() Colin Hayes Excel Discussion (Misc queries) 1 March 17th 10 03:07 AM
How can i make excel to highlight today using conditional formating Kinghart Excel Worksheet Functions 4 November 17th 08 05:58 PM
Highlight the day of today Jaydubs Excel Discussion (Misc queries) 4 November 17th 08 12:32 PM
IF TODAY equals date in cell A10, or if TODAY is beyond that date SoupNazi Excel Worksheet Functions 4 April 23rd 07 01:14 AM
If TODAY = [Certain Date in Row], Highlight this row? sasquatchbill Excel Discussion (Misc queries) 4 August 1st 06 05:53 PM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017