Index Collect with date within 7 days

Here is the setup:

I have two sheets:

  • November Schedule (where the formula will live)
    • Columns
      • Completion Date (Date Column)
      • Hidden Lead Tech (Formula lives here)
  • Historical Roster
    • Columns
      • Created (Date Column)
      • Lead Tech
        • {Historical Roster Range 1} is the Lead Tech column
        • {Historical Roster Range 2} is the Created column

I have this formula working but only if the dates match:

=IFERROR(INDEX(COLLECT({Historical Roster Range 1}, {Historical Roster Range 2}, [Completion Date:]@row), 1), "")

I need the formula to return the Lead Tech if the Completion Date is within the last 7 days of the Created Date.

Thanks in advance for the help.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!