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.

Jake Kenyon

@Dave Wilson I came across this over a year later when I ended up with the same need as you originally had, so I wanted to put it here in case you or anyone else is looking for an answer. I still used prorate to help me determine which columns should receive a value using IF criteria, and if it was above zero, I then referenced the split out $/month as the value that got filled. It needs to be updated for each month to have the correct month number, but here is the one I used for January, adapted to your screenshot:

=IF(PRORATE([$/Month]@row, [Start]@row, [End]@row, DATE([Year One]@row, 1, 1), DATE([Year One]@row, 1, 31)) > 0, [$/Month]@row)

I am referencing a column for the year so that it can automatically update based on the provisioning date. I'm sure you found your solution some time ago, but I hope this helps someone else struggling with this problem in the future!