How to Build a Resource Management System in Airtable

Every project-based business hits the resource problem early. You've got three designers and four projects in motion. Two of those projects need the same designer for the same week. By the time someone notices, one client is being told to wait while the other is panicking about why their work isn't moving.

A resource management system makes this visible before it happens. Who's free? Who's over-allocated? When will the senior dev be available to start the next project? In Airtable, you can build the answer in a few hours.

This guide walks through a complete system for an agency or project-based team — people, capacity, allocations, time off, and the views and automations that catch problems early.

The Schema

Five tables.

People

• Name (primary)
• Email
• Role (single-select: Designer / Developer / PM / Strategist / etc.)
• Skills (multi-select)
• Weekly Capacity (number, hours — typically 40)
• Hourly Rate (currency, optional — used for project pricing)
• Start Date, End Date (when they joined or left)
• Active (checkbox)
• Manager (collaborator, optional)
• Allocations This Week (rollup — see below)
• Utilization This Week (formula)

Projects

• Name (primary)
• Client (linked → Clients, if you have a CRM)
• Status (single-select: Pipeline / Active / On Hold / Complete)
• Start Date, End Date
• Budget Hours (number)
• Allocated Hours (rollup of allocations)
• Logged Hours (rollup of time entries — if you track time)
• Project Manager (linked → People)

Allocations (the heart of the system)

• ID (primary, autonumber)
• Person (linked → People)
• Project (linked → Projects)
• Week Of (date, Monday of the week)
• Hours (number)
• Notes

Time Off

• ID (primary, autonumber)
• Person (linked → People)
• Start Date, End Date
• Type (single-select: Vacation / Sick / Public Holiday / Other)
• Hours (formula — automatic based on date range and Weekly Capacity)
• Approved (checkbox)

Time Entries (optional, if you track actuals)

• ID (primary, autonumber)
• Person (linked → People)
• Project (linked → Projects)
• Date
• Hours (number)
• Notes

For the relational patterns, see Linked Records Explained.

The Allocation Concept

The Allocations table is what makes resource management possible. Each record is one row of "this person is working this many hours on this project in this week."

A new project starts and you allocate:

• Sarah (designer), Acme Rebrand, Week of May 4, 30 hours
• Sarah, Acme Rebrand, Week of May 11, 30 hours
• Mike (developer), Acme Rebrand, Week of May 11, 10 hours
• Mike, Acme Rebrand, Week of May 18, 30 hours

Now you have a precise forward plan. Sarah is at 30 hours on Acme that week; she has 10 hours left for other projects. Mike is at 10 hours that week, mostly free.

The rest of the system is just rollups and views on this table.

Calculating Utilization

Two key calculations per person per week.

Total Allocated Hours this week. Rollup on People:

• Aggregation: SUM
• Source: Allocations Hours
• Condition: Allocations Week Of = this week

Airtable's rollup filter syntax uses IS_SAME({Week Of}, TODAY(), 'week') to compare the allocation's {Week Of} value against the current week from TODAY(). This returns true for any date in the same calendar week as today. Airtable doesn't support JavaScript-style ternary expressions in formulas — stick to native functions like IS_SAME, DATEADD, and TODAY rather than trying to reach for ?: syntax. As an alternative, you can maintain a "current week" date field on a Settings table that an automation updates daily, then reference it with a simple {Week Of} = {Current Week} comparison.

Utilization This Week. Formula on People:

IF(
    {Weekly Capacity} = 0,
    'N/A',
    ROUND({Allocations This Week} / {Weekly Capacity} * 100) & '%'
)

A status formula categorizes:

IF(
    {Allocations This Week} > {Weekly Capacity},
    '🔴 Over Allocated',
    IF(
        {Allocations This Week} >= {Weekly Capacity} * 0.9,
        '🟡 Near Capacity',
        IF(
            {Allocations This Week} >= {Weekly Capacity} * 0.5,
            '🟢 Allocated',
            '⚪ Under Allocated'
        )
    )
)

Now the People table shows utilization status at a glance.

The Weekly Planning View

The most-used view in the whole system. It shows allocations as a grid: people on rows, weeks on columns, hours in cells.

Airtable doesn't have a native pivot view, but you can approximate it with these approaches.

Approach 1: Multiple rollups (per-week). On each Person, build rollups for "Hours This Week", "Hours Next Week", "Hours Week +2", "Hours Week +3", etc. The Person record then shows the next four weeks of allocation in adjacent columns. Simple, works for short planning horizons.

Approach 2: Timeline view on Allocations. A Timeline view of the Allocations table, grouped by Person, with each allocation drawn as a bar across its week. Visual and intuitive. Best for visual planners.

Approach 3: External tool with sync. If you regularly need pivot-table-style planning, sync allocations to a Google Sheet via Make and build the pivot there. The plan happens in the sheet; allocations live in Airtable.

Most teams use Approach 2 (Timeline) for visual planning and Approach 1 (rollups) for at-a-glance utilization in the People grid.

Time Off Handling

Time off subtracts from a person's available capacity. The pattern:

1. Each time-off record has Start Date, End Date, and a calculated Hours field based on the date range and the person's Weekly Capacity.
2. A rollup on People sums Time Off Hours for the current week.
3. Effective Capacity is calculated as Weekly Capacity minus Time Off Hours.
4. Utilization is then calculated against Effective Capacity, not the raw Weekly Capacity.

When someone takes Friday off:

• They've reduced their effective capacity by 8 hours (1 day) for that week.
• If they were already allocated 40 hours, they're now over-allocated and the status flips to red.
• The view filtered to over-allocated people surfaces the problem.

Approval workflows for time off can be added via automations — manager gets a Slack notification, approves, the record's Approved checkbox flips, the allocation rolls forward.

Forward Planning Workflow

A typical Friday-afternoon planning session for an agency PM:

1. Open the People table sorted by Active = True.
2. Look at each person's utilization for the next 2-3 weeks.
3. Spot the gaps (someone at 20/40 hours next week) and the over-allocations (someone at 50/40 hours week-after-next).
4. Open the Projects table, filter to Active projects.
5. For each project, review allocated hours vs budget hours and the timeline.
6. Adjust by creating, editing, or deleting Allocation records.

By the end of the session, the next 2-3 weeks are planned out. The team knows what they're working on. The PM knows where the capacity gaps are.

A Sunday-evening Make automation can email each person their week's allocations: "Here's what you have planned for the week of May 4 — 20 hours on Acme Rebrand, 15 on Beta Co Website, 5 hours unallocated." Reduces "what am I working on this week?" Slack messages.

Over-Allocation Alerts

The most useful automation in this system: a daily check for over-allocations.

1. Trigger: Schedule (daily, 7am).
2. Find Records on People where Utilization Status = '🔴 Over Allocated' for the current week.
3. If any results, send a Slack message to the operations channel: "⚠️ X people are over-allocated this week — [Sarah (50hrs/40), Mike (45hrs/40)]. Adjust allocations or extend timelines."
4. Send DM to each over-allocated person and their manager.

Catching over-allocation early lets you adjust. Catching it after the week starts means someone works late or a project slips.

A second automation runs when an Allocation is created or updated:

1. Trigger: Watch Records on Allocations (created or updated).
2. Find Record on People = the allocation's person.
3. Filter: if the person's Allocations This Week now exceeds Weekly Capacity, send a Slack message.

Immediate feedback. The PM creates an allocation that tips someone over, gets pinged within seconds, and can adjust on the spot.

Project Health View

A second use of the data: monitoring projects.

For each Project, compute:

• Allocated Hours (rollup)
• Logged Hours (rollup from Time Entries)
• Hours Remaining = Budget Hours - Logged Hours
• Percent Complete = Logged / Budget * 100
• On Track formula:

IF(
    {Logged Hours} > {Budget Hours},
    '🔴 Over Budget',
    IF(
        {Logged Hours} / {Budget Hours} > (DATETIME_DIFF(TODAY(), {Start Date}, 'days') / DATETIME_DIFF({End Date}, {Start Date}, 'days')),
        '🟡 At Risk',
        '🟢 On Track'
    )
)

The formula compares the percent of budget used vs the percent of timeline elapsed. If you've used 80% of budget but only 40% of timeline, you're at risk.

A weekly automation summarizes project health into a dashboard or a Friday email to leadership.

The Dashboard

Build an Interface Designer page with:

• Team Utilization This Week (chart: bar per person)
• Over Allocated (filtered list, this week)
• Under Allocated (filtered list — capacity available for new work)
• Active Projects (filtered list with on-track status)
• Time Off This Week (filtered list)
• Forecast: Next 4 Weeks (chart showing total team hours allocated vs total team capacity)

The Forecast chart is the leadership question — do we have capacity to sell more work next month, or are we already booked?

A second interface for PMs filtered to "current user" shows their assigned projects, the allocations they've made, and any over-allocations they've caused.

Integrating With Project Management

The resource system isn't useful in isolation. It works because it's connected to your Projects.

A natural integration: when a Project moves from Pipeline to Active, an automation creates a placeholder Allocations record for each role mentioned in the project. The PM then refines those allocations into specific weeks and people. The default values save time on the routine projects.

A reverse integration: when a Project moves to Complete, an automation deletes future allocations on that project. Frees up capacity automatically.

Common Mistakes

A few that show up in real implementations.

Allocating in too-fine detail. Daily allocations sound precise but are exhausting to maintain. Stick to weekly for forward planning. Use Time Entries for actuals.

Not maintaining Weekly Capacity. A new hire shows up with no Weekly Capacity set, gets allocated to projects, but utilization shows N/A because the divisor is missing. Make capacity a required field on new People.

Allocations without ranges. Some teams try to allocate a person to a project as "30 hours over the whole project" without specifying which weeks. The utilization math breaks. Always tie allocations to specific weeks.

Ignoring partial-week starts. A project starts on Wednesday. The first week's allocation should account for only 3 days of work, not a full week. Build this nuance in if your projects often start mid-week.

Treating allocations as commitments. They're plans, not promises. The team should be free to adjust if a project changes scope or a higher priority emerges. The system surfaces the trade-offs; humans decide.

When to Use a Dedicated Tool

The Airtable resource management system covers most agencies up to about 50 people. Move to Float, Resource Guru, Forecast, Mosaic, or a similar tool when:

• You need a polished, drag-and-drop visual planner that non-Airtable users find intuitive
• You have 50+ people and the data volume slows Airtable views
• You need built-in time-tracking with mobile apps and integrations
• Your finance team wants billing reports that the dedicated tool ships with

Airtable is the right starting point because it integrates with everything else (CRM, projects, time tracking, finance). When you outgrow it, the data exports cleanly to whatever tool you move to.

Where to Go Next

For the broader project management context, our project management tutorial covers the Projects-and-Tasks layer this system sits on top of. The Interface Designer guide covers building the dashboards that surface the data daily.

For the relational patterns underpinning the allocation rollups, the linked records guide is the prerequisite reading.