Cash Flow Formula by month - Divide a number over a timeline or TRUE FALSE if a timeline falls in a given month

I am working on a board to track cash flows for my dept. We need to be able to track our total committed spend against the invoices that we’ve paid so far and estimate upcoming cash flows for the agreements where services will be rendered over 2+ months out of the year. I need to track this by month and be able to manipulate the data in the pivot table feature.

So far, I’ve set up the Items in each row by vendor name and have a column to generate PO numbers for each line item. The invoices will be put in as subitems under each corresponding agreement. I’ve set up a mirror column on the item to show the total amount we’ve paid based on the subitem invoices. There is also a formula column that deducts the amount of the agreement from the invoiced amount to get the “remainder” to be paid.

I have also made a “services rendered” timeline column and a column where I can input the “number of months” an item is going to be paid over. I’ve set up a formula that divides the remainder by the number of months that services will be rendered over which gives me the monthly estimated payments.

I need to be able to see at the start/end of each month how much we expect to be invoiced for that month, I think I need a formula for “agreement amount” broken over how many months are covered by the “services rendered” timeline. Is this type of formula possible?

Alternatively, I think the next best thing would be to make a column for every month of the year with a TRUE/FALSE formula for if timeline column value falls within a certain month. This way I can breakdown anything that has a “yes” in the January column and get a more granular look at the cash flow by month and quarter using pivot tables and chart features. Any ideas on how to build this formula?

Hi Chana,

We’d need more details to be able to fully help you out.

As a starter, I’d say you can indeed play with the timeline dates in a formula column.
Use {Timeline#Start} and {Timeline#End} to get the start and end dates.

For instance, if you’d want to calculate the number of days between the 2 dates, you could use:


There are several ways to count the number of months of the timeline though. You’d need to define what would work for you.

What if we could break free of the Formula column and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

Is there a way to calculate the months between two dates?

Also would be great if I can somehow use the numbers widget to calculate items where the timeline falls within a certain month eg: Calculate total for all contracts where the timeline falls within January, Feb and so on.

Alt option would be making a column for each month of the year and using a formula that can calculate if the timeline of that column falls within the specific month using a true/false - then I could use the number widget to filter by those month columns where the value is true.

I’ve been trying to use something like this but not sure that it will pull the right information given that some of the timelines cover 3-6 months:
IF(DATEVALUE({Service Dates#Start})>DATEVALUE(“1/1/2024”),DATEVALUE({Service Dates#End})<DATEVALUE(“1/31/2024”) “yes”, “no”)

I’m fairly new to formulas so any help writing these would be so appreciated!