BUDGETING - Quarterly Forecast by Start and End Date

[Katie_Hill]

Hey Airtable Community,

I have a “Forecast” table in Airtable that I’m using to track various line item costs of a particular project. Each row has the following basic fields: Spend Description, Planned Spend, Start Date, End Date, Q1, Q2, Q3, Q4.

I’d like to be able to use a formula to calculate planned spend by quarter… essentially place line item costs into the Q1, Q2, Q3 and Q4 fields.

I’m new to formulas overall, particularly ones involving dates. I thought I could work around my own limitations by creating a “Spend Month” Multi-Select field, selecting the spend months for each line item, (for example January, February, March) and using the below formula to calculate quarterly spend.

In a field called “Q1” I’m using the formula:

IF(OR({Spend Month}=“January”,{Spend Month}=“February”,{Spend Month}=“March”),{Planned Spend})

However, I’m running into several issues. The most pressing issue is that there are some costs that span multiple quarters (Spend Month = January, February, March, April) which cannot be resolved using my formula.

I have now decided I should go back to the drawing board and use Start Date and End Date to input cost into the right quarter. However, I don’t know where to start with a date formula that involves IF OR.

Could someone kindly guide me? Huge thanks.

[Justin_Barrett]

Before going too far down this rabbit hole, I want to make sure I understand your goal correctly. Say, for example, that you plan to spend a total of $400 on Thing 1 between January 1, 2020, and April 30, 2020. That’s $400 spread across four months, or $100 per month. In your quarterly breakdown, you want the Q1 total to be $300—$100 each for Jan, Feb, and Mar—and Q2 to be $100. Is that correct?

[Katie_Hill]

Thank you for responding @Justin_Barrett! Yes, exactly right. The cost is “straight lined” ie divided equally by month — $100 each month, using your example, with Q1 total to be $300 and Q2 to be $100.

[Justin_Barrett]

Sorry for delaying my response. A bit busy on this end.

I’ve almost got a working system, but I forgot to ask something else about your use case: will there ever be a time when {Start Date} will be in one year and {End Date} in the next?