Incoming Requests Reducing Available Time on Chart

[Michael_Rigoni]

I manage a team of laboratory technicians and we recently started using Airtable in an effort to track the total amount of support time we’re providing to departments, projects and even broken down to task type.

One view we’d like to provide to our group is the total amount of man-hours available for new requests by month. The initial thought is that in the form we require our “customers” to fill out we would ask for the expected start date (month) for the task along with the total number of hours they expect us to take to complete said task. Is there a way to have a bar graph that has the month on the X-axis and total available hours on the Y-axis where, when someone submits a request it subtracts that expected time from the available time on the chart? We would publicize this information outside the lab so people can plan ahead.

Thanks in advance!

[Justin_Barrett]

Welcome to the community, Michael! :grinning_face_with_big_eyes: Here’s what I suggest. Using a form, your users will populate a table looking something like this:

When filling out the form, users would pick their department, the month, and how many hours they’re requesting in that month. The date field is a Created Time type, so it fills automatically when the request is submitted. The same department can make multiple requests in the same month, as it will all add up later.

The month is chosen from this table:

You allocate the number of hours that your team can work in a given month. Using a rollup (the {Requested Hours} field), you summarize all of the requests for each month, then use a formula field to subtract that from your allotment. You then use these values in the chart to show everyone what’s left. As new requests come in, they’ll pull from that allotted amount.