=ABS((TODAY()-A2))=MIN(ABS(TODAY()-$A$2:$A$8))
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | 11/15/2015 | 530 | ||
2 | 10/5/2015 | 489 | ||
3 | 6/23/2014 | 20 | ||
4 | 10/15/2015 | 499 | ||
5 | 12/4/2015 | 549 | ||
6 | 7/15/2015 | 407 | ||
7 | 8/2/2014 | 60 | ||
Sheet |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A1 | 1. / Formula is =$B1=MIN($B$1:$B$7) | Abc | ||
A2 | 1. / Formula is =$B2=MIN($B$1:$B$7) | Abc | ||
A3 | 1. / Formula is =$B3=MIN($B$1:$B$7) | Abc | ||
A4 | 1. / Formula is =$B4=MIN($B$1:$B$7) | Abc | ||
A5 | 1. / Formula is =$B5=MIN($B$1:$B$7) | Abc | ||
A6 | 1. / Formula is =$B6=MIN($B$1:$B$7) | Abc | ||
A7 | 1. / Formula is =$B7=MIN($B$1:$B$7) | Abc |
I just found this formula online but it does not work for me:
=SMALL(IF(($A$2:$A$15>TODAY()),$A$2:$A$15),1)
A2:A15 are my dates.
Assuming that A2:A8 contains the data, try the following...
1) Select A2:A8
2) On the Ribbon, select Home > Styles > Conditional Formatting > New Rule
3) Under Select a Rule Type, select 'Use a formula to determine which cells to format'
4) Under Edit the Rule Description, enter the following formula:
Code:=ABS((TODAY()-A2))=MIN(ABS(TODAY()-$A$2:$A$8))
5) Click on Format, select your formatting, and click OK
6) Click OK
Hope this helps!
For a formula solution, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...
=INDEX(A2:A8,MATCH(MIN(ABS(TODAY()-A2:A8)),ABS(TODAY()-A2:A8),0))
Adjust the ranges, accordingly.
Hope this helps!
That worked but it highlighted 12/14/15 and did not highlight 6/23/14, as I was hoping.
I am trying to get a date to appear in cell C17.
It should highlight 6/23/14. In fact, I tested it and it does highlight 6/23/14. Make sure that all dates are true date values. You can test whether they're true date values by using ISNUMBER. For example, to test whether A2 is a true date value, the following formula would return TRUE if the value is a true date value...
=ISNUMBER(A2)
Have a look at my last post, which provides you with a formula to return the closest date to today's date.