Formula to Link PTO Tracker to Project Schedule

Jared H.

Hello,

I have created a PTO tracker to log my project team's PTO during the project lifecycle. The PTO tracker that I created is a separate sheet from my project schedule.

Is there a way that I can create some conditional formatting (maybe via a checkbox helper column) to link these two sheets and indicate where there is overlap between a teammate's PTO and a task that they are assigned to?

For example: Person A is scheduled to take PTO from August 7 until August 11, but they have a task assigned to them from August 9 until August 16. Is there a way to write a formula to check a checkbox indicating that there is overlap between those two date ranges? I would like to be able to quickly see if there is a PTO conflict that would take them away from their assigned task - even if just for one day.

Thank you!

Paul Newcome

@Carson Penticuff A much more efficient way to find overlap in dates is to compare End Date column to Start Date "@row" and the other way around.

=COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row)

The above covers every potential variation of overlap and is much less typing.

@Jared H. Try something like this:

=IF(COUNTIFS({PTO Sheet Name Column}, HAS([Assigned To]@row, @cell), {PTO Start Date Column}, @cell<= [End Date]@row, {PTO End Date Column}, @cell>= [Start Date]@row)> 0, 1)

Paul Newcome

@Jared H. Of course the cross sheet references and cell references would need updated, but the only actual syntax change should just be the HAS function needs switching around.

HAS([Column Name]@row, @cell)

changes to

HAS(@cell, [Column Name]@row)

Carson Penticuff

I think this will do what you are looking for.

=IF(COUNTIFS({Assigned to}, [Name]@row, {Start}, AND(@cell >= [PTO Start]@row, @cell <= [PTO Finish]@row)) + COUNTIFS({Assigned to}, [Name]@row, {Finish}, AND(@cell >= [PTO Start]@row, @cell <= [PTO Finish]@row)) + COUNTIFS({Assigned to}, [Name]@row, {Start}, < [PTO Start]@row, {Finish}, > [PTO Finish]@row) >= 1, 1, 0)

This assumes you are placing the checkbox column in the sheet with the PTO dates. You will need to set up three cross-sheet references inside your PTO sheet that point at your Project sheet. {Assigned to}, {PTO Start}, and {PTO Finish}. Additionally, you will need to setup the Name column inside the PTO sheet as a contact column in order for the match to work between the two sheets.

Jared H.

https://community.smartsheet.com/discussion/comment/382465#Comment_382465

Hi Carson, thank you for the quick reply. This formula works well, but unfortunately I was hoping to place the checkbox in the project schedule sheet. Is there an easy way to do that?

Also - this isn't a requirement - but is it possible to be able to identify a conflict if the "assigned to" person shares the task with another person/people? As written, the formula only detects tasks where only the "assigned to" is the task owner, and does not work when the task has multiple owners.

Cheers!

Paul Newcome

@Carson Penticuff A much more efficient way to find overlap in dates is to compare End Date column to Start Date "@row" and the other way around.

=COUNTIFS([End Date]:[End Date], @cell>= [Start Date]@row, [Start Date]:[Start Date], @cell<= [End Date]@row)

The above covers every potential variation of overlap and is much less typing.

@Jared H. Try something like this:

=IF(COUNTIFS({PTO Sheet Name Column}, HAS([Assigned To]@row, @cell), {PTO Start Date Column}, @cell<= [End Date]@row, {PTO End Date Column}, @cell>= [Start Date]@row)> 0, 1)

Jared H.

@Paul Newcome that formula works very well - thank you. I am still working through how I can also put the formula on the PTO Tracker sheet (working backwards), but for now I have exactly what I was looking for.

Thanks again!

Paul Newcome

@Jared H. Of course the cross sheet references and cell references would need updated, but the only actual syntax change should just be the HAS function needs switching around.

HAS([Column Name]@row, @cell)

changes to

HAS(@cell, [Column Name]@row)

Jared H.

@Paul Newcome that worked! Thanks again for your help.

Paul Newcome

Happy to help. 👍️