How to Automatically Track Each Team Member’s Available Hours in Airtable

You are managing a team and multiple projects. Each project requires a specific number of hours, and each team member has a limited weekly capacity.

When you assign a project to someone, you want Airtable to automatically calculate how much of their time is already used and how much is still available.

In this article, I will show you how to create a resource management system in Airtable that tracks each person’s workload and updates their remaining capacity automatically whenever you assign them to a new project.

Setting Up the Tables

For this, you will need three tables.

Let us go through each of these one by one.

People Table

This table stores information about your team members and their total available working hours.

In the People table, add:

• Name – A single line text field for the employee’s name

• Role – A single select field for the employee’s role or designation

• Weekly Capacity (hrs) – A number field to define how many hours the person can work per week

Projects Table

This table stores details about each project your team is working on.

In the Projects table, add:

• Project Name – A single line text field for the project’s name

• Total Hours (hrs) – A number field for the total estimated hours required to complete the project

You can also add optional fields such as Description, Start Date, End Date, or Status depending on your needs.

Assignments Table

This is the junction table that connects People and Projects. Each record in this table represents one person working on one project.

In the Assignments table, add:

• Person – A linked record field connected to the People table

• Project – A linked record field connected to the Projects table

• Assigned Hours (hrs) – A number field to record how many hours that person will spend on the project

Calculating Remaining Capacity

After you have created the three tables, go back to the People table and set up the rollup field.

• Add a new field called Used Capacity (hrs)

• Set the field type to Rollup

• Choose the Assignments table and roll up the Assigned Hours (hrs) field

• Use the rollup formula SUM(values) to calculate the total hours assigned to each person

Then add a formula field called Remaining Capacity (hrs) with this formula:

{Weekly Capacity (hrs)} - {Used Capacity (hrs)}

This will calculate the remaining hours for each person. The value will update automatically as you assign people to new projects.

That’s it. Your resource management system is ready.