Scripting + Workday Formula = PTO Tracking & Project Planning

[VictoriaPlummer]

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 = []

// 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([c,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! 🚀')