How to Spread Income or Costs over Months

CAH

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,

CAH

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))

Julio S.

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

CAH

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))

JamieCope

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

Tina Ciak

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.