# 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,

• ✭✭✭

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

• Moderator

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.

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

• edited 12/02/22

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!