How to Spread Income or Costs over Months
I have multiple rows of data with the following columns: Start Date, End Date, Duration, Cost, Cost / Day and then 12 columns for each month of the year. For each row, I want to find out what proportion of cost will fall into each month. I have found some helpful tips for Excel but when I try to put into Smartsheet, I am getting unparseable error. Please find links and formulas that I have been trying to convert:
More accurate as takes into account working days only: https://www.excel-university.com/excel-formula-to-allocate-an-amount-into-monthly-columns/ =[Daily Amount]@row*((MAX(NETWORKDAYS([Start Date]@row, DATE(2022, 01, 31)),0)-MAX(NETWORKDAYS([Start Date]@row,EOMONTH(DATE(2022, 01, 31),-1)),0))-(MAX(NETWORKDAYS([End Date]@row, DATE(2022, 01, 31)),0)-MAX(NETWORKDAYS([End Date]@row,EOMONTH(DATE(2022, 01, 31),-1)),0))+(EOMONTH(DATE(2022, 01, 31),0)=EOMONTH([Start Date]@row,0)))
Alternative option: https://www.myonlinetraininghub.com/excel-formula-to-spread-income-or-costs-over-months=[Daily Amount]@row*(DATE(2022, 01, 31) = MEDIAN(EOMONTH([Start Date]@row,-1)+1,EOMONTH([End Date]@row,0),DATE(2022, 01, 31)))*(MIN([End Date]@row,EOMONTH(DATE(2022, 01, 31),0))-MAX([Start Date]@row,DATE(2022, 01, 31)-1))
As this will be a formula that I want to use over multiple projects - is there a way that I can use 'Jan' column for Jan 21, Jan 22, Jan 23 etc?
I am relatively new to formulas so any advice would be gratefully received.
Many thanks,
Best Answer
-
Julio,
Thank you for your input. That helped but spread it out over all the months but didn't calculate rate only in months falling between start and end date.
However, I have worked something out as follows, January 2022 for example is:
=[Revenue / Day]@row * ((MAX(DATE(2022, 1, 31) - [Start Date]@row, 0) - MAX(DATE(2021, 12, 31) - [Start Date]@row, 0)) - (MAX(DATE(2022, 1, 31) - [End Date]@row, 0) - MAX(DATE(2021, 12, 31) - [End Date]@row, 0)) + IF(MONTH(DATE(2022, 1, 31)) = MONTH([Start Date]@row), 1))
Answers
-
Hi @CAH ,
There are two possible paths that you may follow to perform these calculations:
Distribute the costs based on working days throughout the year (note that this may result in some discrepancies in the totals due to the resulting decimals in the divisions) - Row 1 in my example below:
- Cost/day formula: =Cost@row / NETWORKDAY(Start@row, End@row) or =Cost@row / NETWORKDAYS(Start@row, End@row) if start is on a non working day or Cost@row/Duration@row
- Each month:
- January: =NETWORKDAY(DATE(2022, 1, 1), DATE(2022, 1, 31)) * $[Cost/day]@row
- February: =NETWORKDAYS(DATE(2022, 2, 1), DATE(2022, 2, 28)) * $[Cost/day]@row (29 for leap-years)
- Etc.
Disregarding working days - Row 2 in my example below:
- Cost/day formula: =COST@row/365 (Or 366 for leap-years)
- Each month:
- January: =31*$[Cost/day]@row
- February: =28*$[Cost/day]@row (29 for leap-years)
- Etc.
Note that when using the NETWORKDAYS and NETWORKDAY Functions, calculations will be based on the working days settings under your project settings.
I hope these formulas can help you achieve what you intend.
Cheers!
Julio
-
Julio,
Thank you for your input. That helped but spread it out over all the months but didn't calculate rate only in months falling between start and end date.
However, I have worked something out as follows, January 2022 for example is:
=[Revenue / Day]@row * ((MAX(DATE(2022, 1, 31) - [Start Date]@row, 0) - MAX(DATE(2021, 12, 31) - [Start Date]@row, 0)) - (MAX(DATE(2022, 1, 31) - [End Date]@row, 0) - MAX(DATE(2021, 12, 31) - [End Date]@row, 0)) + IF(MONTH(DATE(2022, 1, 31)) = MONTH([Start Date]@row), 1))
-
Hi All. Thanks so much for the direction on this. Helped a lot. OK, so three hours later I think we need to add an IF/OR statement to capture the situation where we need multiple year calculations (since this will parse the same regardless of year). Let me see if I can work something up
-
Any updates on this? I really need a solution where I can have just 12 columns in my sheet for Jan - Dec but have it span for several years based on the start and end date columns.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!