Finding Date Closest to Today but Not in the Future

[gambit023]

Hello all, I'm trying to use conditional formatting to highlight the row of data with a date that is closest to today but not in the future.

I am currently using =$A2=TODAY() in conditional formatting, but this will not highlight the date if today's date is not on the list. I would like to highlight the last in the series of dates that's closest to today but not in the future.

Can someone help?

[TABLE="width: 651"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Check #[/TD]
[TD]Payee[/TD]
[TD]Comments[/TD]
[TD] Expense[/TD]
[TD] Deposit[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2018[/TD]
[TD][/TD]
[TD]Deposit[/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 3,500.00[/TD]
[TD="align: right"]$7,144.18[/TD]
[/TR]
[TR]
[TD="align: right"]1/12/2018[/TD]
[TD][/TD]
[TD]Cell Phone[/TD]
[TD][/TD]
[TD] $ 70.00[/TD]
[TD][/TD]
[TD="align: right"]$7,074.18[/TD]
[/TR]
[TR]
[TD="align: right"]1/16/2018[/TD]
[TD][/TD]
[TD]Car Payment[/TD]
[TD][/TD]
[TD] $ 281.58[/TD]
[TD][/TD]
[TD="align: right"]$6,792.60[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD][/TD]
[TD]Credit Card[/TD]
[TD][/TD]
[TD] $ 229.00[/TD]
[TD][/TD]
[TD="align: right"]$6,563.60[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD][/TD]
[TD]Insurance[/TD]
[TD][/TD]
[TD] $ 44.01[/TD]
[TD][/TD]
[TD="align: right"]$6,519.59[/TD]
[/TR]
[TR]
[TD="align: right"]1/21/2018[/TD]
[TD][/TD]
[TD]Withdraw[/TD]
[TD][/TD]
[TD] $ 60.00[/TD]
[TD][/TD]
[TD="align: right"]$1,548.57[/TD]
[/TR]
[TR]
[TD="align: right"]1/29/2018[/TD]
[TD][/TD]
[TD]Gas and Electric[/TD]
[TD][/TD]
[TD] $ 222.25[/TD]
[TD][/TD]
[TD="align: right"]$4,826.32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Max1616]

I don't think this is possible using conditional formatting alone (Though I hope someone proves me wrong). You will have to use either an array formula or vba (I would use an array formula).

Place this formula in an adjacent cell next to the date, and press cntrl+shift+enter to make the equation an array formula:
=IF(A2=MAX(IF(A:A<TODAY(),A:A,0)),1,0)
Array formula should look like this if done correctly {=IF(A2=MAX(IF(A:A<TODAY(),A:A,0)),1,0)}

Drag/Copy down the formula. This should show a 1 for dates that meet your criteria and a 0 for dates that do not (In your example, a 1 would show for the three records with the date 1/21). From there you can use conditional formatting to highlight the rows where the formula produces the number 1.

Hope this helps.

 

[jtakw]

Hi,

Actually, it is possible:

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Check #	Payee	Comments	Expense	Deposit	Balance
2	1/12/2018		Deposit			$3,500.00	$7,144.18		FALSE
3	1/12/2018		Cell Phone		$70.00		$7,074.18		FALSE
4	1/16/2018		Car Payment		$281.58		$6,792.60		FALSE
5	1/21/2018		Credit Card		$229.00		$6,563.60		TRUE
6	1/21/2018		Insurance		$44.01		$6,519.59		TRUE
7	1/21/2018		Withdraw		$60.00		$1,548.57		TRUE
8	1/29/2018		Gas and Electric		$222.25		$4,826.32		FALSE
Sheet7

Cell Formulas
Range	Formula
I2	=A2=LARGE(A$2:A$8,COUNTIF(A$2:A$8,">"&TODAY())+1)

Use my sample I2 formula for your CF formula rule.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G
1	Date	Check #	Payee	Comments	Expense	Deposit	Balance
2	1/12/2018		Deposit			$3,500.00	$7,144.18
3	1/12/2018		Cell Phone		$70.00		$7,074.18
4	1/16/2018		Car Payment		$281.58		$6,792.60
5	1/21/2018		Credit Card		$229.00		$6,563.60
6	1/21/2018		Insurance		$44.01		$6,519.59
7	1/21/2018		Withdraw		$60.00		$1,548.57
8	1/29/2018		Gas and Electric		$222.25		$4,826.32
Sheet27

=$A2:$G8=LOOKUP(TODAY(),$A2:$A8)

 

[CatMama9]

Hi,

Actually, it is possible:

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Check #	Payee	Comments	Expense	Deposit	Balance
2	1/12/2018		Deposit			$3,500.00	$7,144.18		FALSE
3	1/12/2018		Cell Phone		$70.00		$7,074.18		FALSE
4	1/16/2018		Car Payment		$281.58		$6,792.60		FALSE
5	1/21/2018		Credit Card		$229.00		$6,563.60		TRUE
6	1/21/2018		Insurance		$44.01		$6,519.59		TRUE
7	1/21/2018		Withdraw		$60.00		$1,548.57		TRUE
8	1/29/2018		Gas and Electric		$222.25		$4,826.32		FALSE
Sheet7

Cell Formulas
Range	Formula
I2	=A2=LARGE(A$2:A$8,COUNTIF(A$2:A$8,">"&TODAY())+1)

Use my sample I2 formula for your CF formula rule.

=$B2=LARGE(B$2:B$54,COUNTIF(B$2:B$54,">"&TODAY())+1)
only highlights the A column of the row that has the date before or on today. (see snip)

Do you know why?

 

[CatMama9]

Excel 2010
	A	B	C	D	E	F	G
1	Date	Check #	Payee	Comments	Expense	Deposit	Balance
2	1/12/2018		Deposit			$3,500.00	$7,144.18
3	1/12/2018		Cell Phone		$70.00		$7,074.18
4	1/16/2018		Car Payment		$281.58		$6,792.60
5	1/21/2018		Credit Card		$229.00		$6,563.60
6	1/21/2018		Insurance		$44.01		$6,519.59
7	1/21/2018		Withdraw		$60.00		$1,548.57
8	1/29/2018		Gas and Electric		$222.25		$4,826.32
Sheet27

=$A2:$G8=LOOKUP(TODAY(),$A2:$A8)

I tried your solution, and it highlighted no rows. (see snip)

Do you know why?

 

[CatMama9]

=$B2=LARGE(B$2:B$54,COUNTIF(B$2:B$54,">"&TODAY())+1)
only highlights the A column of the row that has the date before or on today. (see snip)

Do you know why?

By the way, when I changed the absolute column to a relative column in that formula for the conditional formatting, it highlighted a (to me) random cells in addition to the correct A column cell. (see snip)

 

[sheetspread]

I tried your solution, and it highlighted no rows. (see snip)

Do you know why?

What did you type in the conditional formatting box? Both number formats must coincide (date or integer).

 

[Fluff]

Try it like

=$B2=LARGE($B$2:$B$54,COUNTIF($B$2:$B$54,">"&TODAY())+1)