Pull PTO Dates on a Project Plan

Jaz693

I have two sheets. One is a project plan that includes task descriptions, assignee, start and end dates. I have another sheet (PTO Calendar) where project team members enter vacation start and end dates.

I have a formula that will check a box on the project plan if the time off from the PTO calendar happens between the start and end dates on the project plan. What I need is the ability to pull the PTO start and end dates from the PTO calendar for the task assignee on project plan when the PTO conflict check box is checked. Any ideas on what that formula may look like? I've tried VLOOKUP's and indexes and having trouble making it work.

Genevieve P.

Hi @Jaz693

You can adapt the formula you have in the checkbox column to be in your INDEX(COLLECT function instead.

An INDEX(COLLECT works like this:

=INDEX(COLLECT({Column to Return}, {1 Column with Criteria}, "Criteria 1", {2 Column with Criteria}, "Criteria 2"}, 1)

So you would use the same Column references and criteria as you have in the checkbox, does that make sense? If this doesn't help, can you post what your checkbox formula is?

Thanks!

Genevieve

Genevieve P.

Hi @Jaz693

Thanks for this information! The criteria in your INDEX(COLLECT should be the same as your COUNTIFS.

Try this:

=IF([PTO Conflict]@row = 1, INDEX(COLLECT({PTO Start Date}, {Master PTO Calendar Name}, Assigned@row, {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row), 1)

Jaz693

Ah,,,, that was the piece, had to use the same criteria. It works! thank you so much!

KPH

Have you tried an INDEX COLLECT combination?

If you need help to set it up, please share a screen shot of your two sheets (hiding any information that you don't want to be publicly visible).

Jaz693

I have tried index, match, but not an index collect. How does that work?

screenshot1 is of the PTO calendar that has the dates for a team member.

screenshot 2 is of the project plan that has the PTO conflict checked and columns to pull the date. I just can't seem to get it pull in

calendar dates.docx

Genevieve P.

Hi @Jaz693

You can adapt the formula you have in the checkbox column to be in your INDEX(COLLECT function instead.

An INDEX(COLLECT works like this:

=INDEX(COLLECT({Column to Return}, {1 Column with Criteria}, "Criteria 1", {2 Column with Criteria}, "Criteria 2"}, 1)

So you would use the same Column references and criteria as you have in the checkbox, does that make sense? If this doesn't help, can you post what your checkbox formula is?

Thanks!

Genevieve

Jaz693

Ah, makes sense it worked. Thank you for providing the format!

Jaz693

Actually I spoke too soon. I entered this formula: =INDEX(COLLECT({PTO Start Date}, [PTO Conflict]@row, 1, {Master PTO Calendar Name}, Assigned@row), 1) and i get #Incorrect Argument Set. but if I take the [PTO Conflict]@ row,1 out, I get a date returned.

I only want the formula to return pto start date for the assignee from the master pto calendar if pto conflict checkbox is checked. I think I have this right?

The PTO conflict checkbox does have a formula too (but it works). Here it is: =IF(COUNTIFS({Master PTO Calendar Name}, HAS(@cell, Assigned@row), {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row) > 0, 1)

Genevieve P.

Hi @Jaz693

Thanks for this information! The criteria in your INDEX(COLLECT should be the same as your COUNTIFS.

Try this:

=IF([PTO Conflict]@row = 1, INDEX(COLLECT({PTO Start Date}, {Master PTO Calendar Name}, Assigned@row, {PTO Start Date}, @cell <= [End Date]@row, {PTO End Date}, @cell >= [Start Date]@row), 1)

Jaz693

Ah,,,, that was the piece, had to use the same criteria. It works! thank you so much!