Hey Y’all,

So, I’d like to incorporate PTO into project planning - here’s how I’m thinking about it, but would love more ideas on how to make this most actionable.

I have a table for projects that has a Start Date and an estimated duration for how long the project should take. To incorporate PTO, I can use the Workday function, which allows me to include a list of holidays formatted as a comma-separated string of ISO-formatted dates.

To use the Workday function, I’ll need a list of every day that someone is off for PTO. In this example, I’m tracking PTO in a table with fields for Start and End dates. This is a great start, but I need every date in between - a perfect job for a script.

The below script calculates each day a person is on PTO. The “Days Off” then roll up to the person, which is then rolled up to the Projects table when someone is assigned to a project and passed through the Workday function.

Now we can calculate the End Date of a project based on the Start Date, Duration, and PTO for the assigned DRI. We can also create Alerts for when someone is assigned to start a project during their PTO.

Script below, and example base here. How would you use this? What would make this more usable?

let pto = base.getTable('PTO');


let ptoQuery = await pto.selectRecordsAsync();


let ptoRecords = ptoQuery.records;





// The Array to store all record updates


let ptoBackend = e]





// For each PTO Record, calculate the PTO dates between the start and end dates, and add them to an array.


ptoRecords.forEach(function(c){





    let d =  ]


    for (let i = 0; i < c.getCellValue('Duration'); i++){





        let startDate = new Date(c.getCellValue('Date Start'));


        let newDate = new Date(startDate.setDate(startDate.getDate() + i));


        d.push(newDate.toISOString().substring(0,10));


        


    }


    ptoBackend.push(pc,d])





})





// Format the array for the UpdateRecordsAsync function


let updateRecords = ptoBackend.map( c => ({


    id: c<0].id,


    fields:{


    'Days Off': c 1].join(),


    },


})


);





// Update Records


await pto.updateRecordsAsync(updateRecords);





output.markdown('# Done! ')