Index Collect with a Date Range

Pattie

Hi,

I am working on a tracker for our new employees to ensure our managers are completing a weekly check-in with them for 5 weeks.

I need to match their name, then match collect the created date from the check-in form and confirm it falls in between their start date and date helper field (start date +7 days). I will need to continue this formula for week 2, week 3, and so on.

=INDEX(COLLECT({Check-in form Range created date}, Name@row, ={ Check-in form Range Employee Name}, { Check-in form Range created date }, >=[Hire Date]@row, { Check-in form Range created date }, <=[Week 1 Date Helper]@row), 1)

Hudson_Miller

@Pattie

Sorry that your team is hip faking the system. I think this formula will do the trick. I'll let you advance it for your 5 checkin's.

Hudson

=INDEX(COLLECT({Check_In|Completed}, {Check_In|Employee}, @cell = [Primary Column]@row, {Check_In|Completed}, @cell >= [Hire Date]@row, {Check_In|Completed}, @cell <= [Week 1 Due]@row), 1)

Hudson_Miller

@Pattie

I know this doesn't answer your question specifically but have you considered adding a field on the check in form that is a drop down with Week 1-Week 5. That way when they submit the form you know what week the form is being submitted for. Then you can do an index collect in each column to pull the created date by looking up the person's name and "Week 1" and then in the next check in column the formula would increment to the person's name and "Week 2". It would be a much simpler way to 'see' that the checkin's are being done in your check in table as you can then build reports or filters by employee that show the dates of the checkin's should someone want to see them as a grouping.

Best,

Hudson

Pattie

@Hudson_Miller

Great thought!

We do have that field in the form, but have experienced managers going in and doing them all at the end of the five weeks. We added the created date field to ensure they are completing them during the actual week.

Hudson_Miller

@Pattie

Sorry that your team is hip faking the system. I think this formula will do the trick. I'll let you advance it for your 5 checkin's.

Hudson

=INDEX(COLLECT({Check_In|Completed}, {Check_In|Employee}, @cell = [Primary Column]@row, {Check_In|Completed}, @cell >= [Hire Date]@row, {Check_In|Completed}, @cell <= [Week 1 Due]@row), 1)

Pattie

@Hudson_Miller

LOL, trust but verify.

Not sure what I am doing wrong. I made sure all my date fields are restricted to dates only and converted the created date to date only. Still getting #unparseable error. I have also tried changing it to match the employee on the ID number as well and still get the same error.

=INDEX(COLLECT({Completed Pulse Checks Range 3}, {Completed Pulse Checks Range 2} , @cell = [Work Contact: Work Email]@row, {Completed Pulse Checks Range 3},@cell >= [Hire/Rehire Date]@row,{Completed Pulse Checks Range 3}, @cell <=[week 1 date range]@row}),1)

Hudson_Miller

@Pattie

Looks like you have an extra Curley bracket at the end of the @row that doesn't need to be there.

Hudson