How do you spread costs evenly across months of project?
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?
Answers
-
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!
-
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?
-
I'm not sure I follow, are you asking to format the duration column differently?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!