Index Collect with date within 7 days

Justin Mauzy

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.

Kelly Moore

Hey @Justin Mauzy

Will this work for you?

=IFERROR(INDEX(COLLECT({Historical Roster Range 1}, {Historical Roster Range 2}, AND(ISDATE(@cell), @cell <= [Completion Date:]@row, @cell >= [Completion Date:]@row - 7)), 1),"")

Kelly

Kelly Moore

Hey @Justin Mauzy

Will this work for you?

=IFERROR(INDEX(COLLECT({Historical Roster Range 1}, {Historical Roster Range 2}, AND(ISDATE(@cell), @cell <= [Completion Date:]@row, @cell >= [Completion Date:]@row - 7)), 1),"")

Kelly

Justin Mauzy

That worked perfectly. Thank you for the quick response.