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

FoxFoxa

=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

or

=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

Paul Newcome

Did you also make sure both of the columns housing the dates are set as date type columns in the column properties? If so, exactly how are they being populated?

emilynduran

I think a Vlookup could solve this issue:

=VLOOKUP([Last Day Worked]@row, [Start Date]1:[Pay Period]24, 3, true)

Just adjust the column names if needed and I counted 24 rows on your second picture.

FoxFoxa

It doesn't come up with a result, some time the number will be between 1-14 or 15-31, it might not be on the certain date that is why i want it bring up PP#

Paul Newcome

The circular reference error is because you are referencing the cell the formula is in within itself. Using Test@row in the Test column what's causing it.

Did you mean to use [Last Day Worked]@row instead?

Geo Villa

Hello @FoxFoxa , Hope this helps!

FoxFoxa

I can get this working with Excel, I can not get this working with Smartsheet. Thank you for point out the circular error, i fixed the formula and now i get #invalid Value in Smart sheets

=INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, [Last Day Worked]@row >= @cell, {Payroll End date Range 7}, [Last Day Worked]@row <= @cell), 1)

Paul Newcome

That particular error means it is not getting a match.

Looking closer at your screenshots... It looks like you have the Start Date in the Primary Column of the reference sheet. This means they are being stored as text strings and as such cannot be used for date based calculations. Make sure that all columns housing dates are in date type columns.

FoxFoxa

I changed the Primary Column to Payperiod but now im getting #invalid Value with the new changes.

=INDEX(COLLECT({Payroll End date Range 5}, {Payroll End date Range 7}, [Last Day Worked]@row >= @cell, {Payroll End date Range 6}, [Last Day Worked]@row <= @cell), 1)

Paul Newcome

Did you also make sure both of the columns housing the dates are set as date type columns in the column properties? If so, exactly how are they being populated?

FoxFoxa

All columns (start date, end date and last day worked) are set to date type columns.

How are they being populate? Just typed in? - I am not understanding the question.

FoxFoxa

@Paul Newcome I redid the formula and it worked. Woohoo, thank you for walking me through this!

Paul Newcome

Happy to help. 👍️