Forum Discussion
Solved
Formula to return a date within a range of dates
Hello Experts, I have a table with a payment date in column A. I need to return a date found in a named range "Conversion" if that conversion date is in the same quarter that the payment date is...
Hi Tony, please use XLOOKUP().
I have combined year and quarter for the correct result.
=XLOOKUP(YEAR([@[Payment Date]])*10+ROUNDUP(MONTH([@[Payment Date]])/3,0),ROUNDUP(YEAR(Table2[Conversion])*10+MONTH(Table2[Conversion])/3,0),Table2[Conversion],"",1)
- thats amazing. thank you for the response.
I have a follow up though.
I am now backtracking a bit since I notice that the missing quarter was an overlook on my part. I now see that the Q1 2023 is present.
How could the formula be modified to return "" instead of the next quarter? I studied the formula but it way out of my league.
thank you very much.You only have to delete the 5th parameter. The default here is the exact search. You can simply remove the parameter ",1".
=XLOOKUP(YEAR([@[Payment Date]])*10+ROUNDUP(MONTH([@[Payment Date]])/3,0),ROUNDUP(YEAR(Table2[Conversion])*10+MONTH(Table2[Conversion])/3,0),Table2[Conversion],"")- worked perfectly! thank you very much for the help!
