Hello. I am new to Airtable and am trying to “convert” an existing capacity planning & forecast spreadsheet into the Airtable platform. At this time, I have two main data sets that I must integrate.
First: I have an employee work capacity (hrs/day) that I need to have the capability to dynamically update with expected attrition, holidays, new hires, reductions in hours due to school schedules, etc. with an outlook over the next year.
Second: I also have a large database of projects that contains the expected work requirement (hrs/project) for each project with defined delivery dates. Separately, each of these seem to be possible to create and manage in Airtable.
However, my challenge starts with the integration. I need the capability to start at Day X (let’s say it is June 1st), pull the projects that are due around that time, pull the daily work capacity around that time, work back from the delivery date of the project to determine the start date based on the available capacity (hr/day) and estimated work requirement (hrs/project), and then start an iterative process moving froward from June 1 for every project (using the same process above) for at least 6 months in the future. This is used to ultimately determine if we have the capacity to deliver the projects on time, and if not, how many days late do we expect to be (or how much additional capacity do I need to deliver on time).
This is relatively easy in a spreadsheet with a series of “index-match” type formulas, as well as running/iterative formulas that would proceed down a column (or field) for every project. However, I can’t figure this out in Airtable. Does anyone have any suggestions or an example of something similar that you have created?
If it were a seamless process, my other potential option would be to automatically export the data into a spreadsheet and import the calculated fields back into Airtable for viewing/integration with the rest of our data.
Thanks in advance for any help you may be able to offer.
