Formula to Link PTO Tracker to Project Schedule
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!
Best Answers
-
@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. 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)
Answers
-
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.
-
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!
-
@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 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!
-
@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)
-
@Paul Newcome that worked! Thanks again for your help.
Help Article Resources
Categories
Check out the Formula Handbook template!