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.
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.
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?
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.