Hello, newbie to Airtable. I am trying to build a CRM tool that also forecasts future period recurring revenues.

We have projects that often span 2-3 years. I would like to allocate the future revenue (equal monthly amounts is fine) across 12/31/XX fiscal year time periods.

My inputs are:

Start date

End date

Monthly revenue

I’d like my outputs to be:

• Prior Period Revenue (any revenue prior to this fiscal year)


• Current Fiscal Year (e.g. 12/31/2020) Revenue


• Next FY Revenue


• Backlog Revenue (anything beyond next fiscal year)

Let’s assume a hypothetical project as follows:

Start date: July 1, 2019

End Date: March 31, 2022

Monthly revenue: $10,000

I would expect the following results:

• Prior Period Revenue: $60,000


• Current FY Revenue: $120,000


• Next FY Revenue: $120,000


• Backlog Revenue (anything beyond next fiscal year): $30,000

I have been able to build this in excel but would really like to manage this process in Airtable. But I am stumped with Airtable date formulae. I think it boils down to this: how do I allocate months in a date range to different fiscal years?

I appreciate any thoughts.