Formula for returning value based on whether a date is between two dates


=INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, Test@row >= @cell, {Payroll End date Range 7}, Test@row <= @cell), 1)

Result: #Circular Reference


=IFERROR(INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, ISDATE(@cell), {Payroll End date Range 7}, ISDATE(@cell), {Payroll End date Range 5}, MAX(@cell) <= [Last Day Worked]@row, {Payroll End date Range 7}, MIN(@cell) >= [Last Day Worked]@row), 1), "No Matching Dates")

Result: "No Matching Date" (due to iferror)

I am trying to retrieve which pay period my date is inbetween.

Payroll deadline or test should reply with PP1-24

What am i doing wrong with formula


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!