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
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
Best Answer
-
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?
Answers
-
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.
-
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#
-
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?
-
Hello @FoxFoxa , Hope this helps!
-
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)
-
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.
-
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)
-
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?
-
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.
-
@Paul Newcome I redid the formula and it worked. Woohoo, thank you for walking me through this!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!