Finding Nearest Upgrade Date

[Justinian]

I have a list of phone upgrade dates:

11/15/15
10/05/15
6/23/14
10/15/15
12/4/15
7/15/15
8/2/14

I need Excel to look at today's date and highlight the nearest upgrade date. How do I do that?

 

[Domenic]

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:

=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!

 

[Justinian]

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.

 

[AlphaFrog]

This uses a helper column B to determine the nearest date, and conditional formatting to highlight the date for minimum value from column B

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

 

[Domenic]

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.

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!

 

[Justinian]

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:

=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!

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.

 

[Justinian]

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!

When I hit Ctrl + Shift + Enter, I get this: ######################################.

 

[Domenic]

That worked but it highlighted 12/14/15 and did not highlight 6/23/14, as I was hoping.

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)

I am trying to get a date to appear in cell C17.

Have a look at my last post, which provides you with a formula to return the closest date to today's date.

 

[Justinian]

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.

I tried that but all that returned in C17 is ##########################.

 

[Justinian]

My apologies! I was not making this an array formula.

This works perfectly now. Thank you!!