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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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

  • Carson Penticuff
    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.

  • 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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!