formula to find the date to today

[tonywatsonhelp]

Hi everyone,
in column B I have a list of different dates B2:B200
in D4 I have todays date.
I want a formoular to find the nearest date to today date before today
please help if you can
Thanks
Tony

 

[Joe4]

In Excel 2019, they introduced the MAXIFS function (MAXIFS function - Microsoft Support).

So try this:

=MAXIFS(B2:B200,B2:B200,"<="&D4)

 

[Dave Patton]

An example with expected results would be helpful.

T202212a.xlsm
	A	B	C	D	E
1		5-Sep-22
2		10-Nov-22
3		20-Nov-22
4		2-Dec-22		2-Dec-22	20-Nov-22
5		15-Dec-22
1c

Cell Formulas
Range	Formula
E4	E4	=VLOOKUP(D4-1,B2:B200,1,1)

 

[etaf]

are the dates in order ?

=index(b2:b200,match(today(),b2:b200,1))

Book3
	A	B	C
1
2
3		11/12/22	11/28/22
4		11/16/22
5		11/20/22
6		11/24/22
7		11/28/22
8		12/3/22
9		12/7/22
10		12/11/22
11		12/15/22
12		12/19/22
13		12/23/22
14		12/27/22
15
Sheet1

Cell Formulas
Range	Formula
C3	C3	=INDEX(B3:B14,MATCH(TODAY(),B3:B14,1))
B3	B3	=TODAY()-20
B4:B7,B9:B14	B4	=B3+4
B8	B8	=B7+5