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! ')