How to calculate my teams availability within a week

Jasmine Moore

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?

Julie Fortney

Hi @Jasmine Moore, the WEEKNUMBER function will likely come in handy for this: https://help.smartsheet.com/function/weeknumber

If you'd like additional help, could you please provide screenshots of your marketing resource sheet (with any sensitive info marked out) so I can see how your information is laid out?

It would be great to see your marketing worklist also, but task lists are usually pretty straightforward. ;)

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt