How do you spread costs evenly across months of project?

Dave Wilson

I did some searching on this topic and came across a couple formulas that I've tried and I must be missing something.

The formula I've used is as follows:

=IF(AND(MONTH($Start@row) <= 1, YEAR($Start@row) <= 2024, MONTH($End@row) >= 1, YEAR($End@row) >= 2024), $[$/Month]@row)

As long as the start and end are in the same year it will put the costs in the correct month. Whent the end date is the following year it doesn't seem to work. Anyone have a solution for this formula?

Jake Kenyon

Have you tried the prorate formula at all for this? It is used to evenly distribute data across a range such as what it looks like you're trying to do here and seems like it would be a perfect fit!

Dave Wilson

Thanks Jake. That seems to have corrected it.

This is just me being particular, but how can I get the cost in the duration columns to be a number like what is shown in the $/month column?

Jake Kenyon

I'm not sure I follow, are you asking to format the duration column differently?

Dave Wilson

Take the 2nd line down for example. If the contract is $1200 for 12 months, the columns months associated with the work duration, ie April 2024 - April 2025, the value would be $100 in each of those months. With the formula right now it has a value of $98.36 for April instead of $100.