Return Date that falls within 10 days of another date


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.


Best Answer

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 04/27/23 Answer ✓

    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), "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!