How to calculate my teams availability within a week

I'm trying to calculate what my team's remaining availability is within a working week.

I've got one sheet (marketing resource) which calculates the maximum capacity per week (contracted working days - annual leave booked) both as a whole team and per employee.

Then I've got a second sheet (marketing worklist) which lists all our tasks. Each task is assigned to an employee, has a start and end date and has a task duration.

I'd like to create a formula which searches each row (task) within the marketing worklist based on the end date. Then add up all the task durations of the tasks due to end within a working week. Then deduct the total task duration for a week from the availability of the week. Then repeat this for every week of the year.

By the end, I'd like to see the number of days we are either over or under capacity each week for the team. It would be an added benefit if I could also see it per individual.

Does anyone know what formula I should be using?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!