Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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.

Tags:

Best Answer

  • Community Champion
    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), "")

Answers

  • Community Champion

    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)

  • ✭✭

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

  • Community Champion
    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!

Trending in Formulas and Functions