How to split a value into monthly amounts using start and end dates?

Matt May

Hello,

My question is: I would like to split up the value in the "estimated budget" column into the the monthly columns using a formula that is based on the start and end date columns.

Is this possible?

Thanks a lot!

Joanna Waligora

Hi,

Chances are you're after the Prorate function (https://help.smartsheet.com/function/prorate).

Example for January:

=PRORATE(

[Estimated Budget (including markup but pre-tax)]@row, 

[Projected Start Date]@row, [Projected Completion Date]@row, 

DATE(2020, 1, 1), DATE(2020, 1, 31))

Aside: it would be nice to auto-calculate eom here, but as far as I know there's' no dedicated function for it, and using the workaround is likely too cumbersome to be beneficial...nonetheless, here's a reference: https://community.smartsheet.com/discussion/6308/how-to-get-eom-end-of-month

Matt May

This is perfect, thank you!

Joanna Waligora

Hi,

Chances are you're after the Prorate function (https://help.smartsheet.com/function/prorate).

Example for January:

=PRORATE(

[Estimated Budget (including markup but pre-tax)]@row, 

[Projected Start Date]@row, [Projected Completion Date]@row, 

DATE(2020, 1, 1), DATE(2020, 1, 31))

Aside: it would be nice to auto-calculate eom here, but as far as I know there's' no dedicated function for it, and using the workaround is likely too cumbersome to be beneficial...nonetheless, here's a reference: https://community.smartsheet.com/discussion/6308/how-to-get-eom-end-of-month

Matt May

This is perfect, thank you!