Return closest date

[288enzo]

I have a lengthy spreadsheet, I need to find which of two dates is the closest to a third date.

As an example, in "T" I would like to return the date from either "R" or "S" that is closest to "M" (positive or negative difference).

	M	N	O	P	Q	R	S	T
19	10/17/2022	Y			2	10/14/2022	10/18/2022	Closest date goes here
KPI Data (Pure Retail)

Cell Formulas
Range	Formula
O19	O19	=IF(LEFT(N19,1)="Y","",(M19-TODAY())*-1)
P19	P19	=IF(N19<>"","",IF(M19+10<TODAY(),"Expired",M19+10))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2:O9463	Cell Value	=10	text	NO
O2:O9463	Expression	=AND(ISNUMBER(O2),O2>10)	text	NO
P2:P9463	Cell Value	contains "Expired"	text	NO
P2:P9463	Expression	=AF2=TODAY()	text	NO

 

[288enzo]

I wrote a macro that works, but would still like to know if there is a formula that could do the same trick.

Thank you.

Sub test4()

    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

    For x = 2 To lastrow

        If Range("Q" & x) > 1 Then

            one = Range("R" & x).Value - Range("M" & x).Value
            two = Range("S" & x).Value - Range("M" & x).Value

            If one < 0 Then one = one * -1

            If one < two Then
                Range("T" & x).Value = Range("R" & x).Value
            Else: Range("T" & x).Value = Range("S" & x).Value
            End If
    
        End If

    Next x

End Sub

 

[kevin9999]

Book1
	M	N	O	P	Q	R	S	T
19	10/17/2022	Y			2	10/14/2022	10/18/2022	10/18/2022
Sheet2

Cell Formulas
Range	Formula
O19	O19	=IF(LEFT(N19,1)="Y","",(M19-TODAY())*-1)
P19	P19	=IF(N19<>"","",IF(M19+10<TODAY(),"Expired",M19+10))
T19	T19	=INDEX(R19:S19, MATCH(MIN(ABS(R19:S19-M19)), ABS(R19:S19-M19), 0))

 

[288enzo]

Book1
	M	N	O	P	Q	R	S	T
19	10/17/2022	Y			2	10/14/2022	10/18/2022	10/18/2022
Sheet2

Cell Formulas
Range	Formula
O19	O19	=IF(LEFT(N19,1)="Y","",(M19-TODAY())*-1)
P19	P19	=IF(N19<>"","",IF(M19+10<TODAY(),"Expired",M19+10))
T19	T19	=INDEX(R19:S19, MATCH(MIN(ABS(R19:S19-M19)), ABS(R19:S19-M19), 0))

Thank you, I had found something similar but was organizing the formula incorrectly.

 

[kevin9999]

Thank you, I had found something similar but was organizing the formula incorrectly.

You're welcome, and thanks for the feedback