Return Date that falls within 10 days of another date

CoryG

So essentailly we have 2 sheets. On 1 sheet we have a list of test dates(call it test dates). Then on the main sheet we a column for due dates for certain documents where if not turned in Student would be locked out and can not take tests. So we want to have another column on the main sheet that pulls dates from the test dates sheet if a test date is within 10 days of a date in the due documents column.

Hollie Green

Found the issue with it. It's the +10 it needs to be in parenthesis. I also added Iferror formula and it just leaves it blank if the date doesn't occur within the timeframe. Also make sure the column your formula is in is set up as a Date column.

=IFERROR(INDEX(COLLECT({Test Date}, {Test Date}, <=([Due Date]@row+10)), 1), "")

Also if you need to add additional search criteria you can add it to the formula. This is assuming the criteria you want to search is on the test sheet and the criteria is on the main sheet where the formula occurs.

=IFERROR(INDEX(COLLECT({Test Date}, {Test Date}, <=([Due Date]@row+10),{Column you want to search},[criteria to search for]@row), 1), "")

Hollie Green

I would try the below. Dates can be finicky so not positive it will work. You will have to create your references.

=Index(Collect({Test Date},{Test Date},<=[Due Date]@row+10),1)

CoryG

https://community.smartsheet.com/discussion/comment/373564#Comment_373564

Hi Hollie thanks for the input. Sadly this gives me a INVALID OPERATION error

Hollie Green

Found the issue with it. It's the +10 it needs to be in parenthesis. I also added Iferror formula and it just leaves it blank if the date doesn't occur within the timeframe. Also make sure the column your formula is in is set up as a Date column.

=IFERROR(INDEX(COLLECT({Test Date}, {Test Date}, <=([Due Date]@row+10)), 1), "")

Also if you need to add additional search criteria you can add it to the formula. This is assuming the criteria you want to search is on the test sheet and the criteria is on the main sheet where the formula occurs.

=IFERROR(INDEX(COLLECT({Test Date}, {Test Date}, <=([Due Date]@row+10),{Column you want to search},[criteria to search for]@row), 1), "")

CoryG

https://community.smartsheet.com/discussion/comment/373582#Comment_373582

That did it thanks!