Budgeting: Spread an expense across months (at the month level)

Tmorrell

Trying to figure out how spread costs equally across a period of months that my users select when entered invoices. The month in my example cannot be a date field (Smartsheet doesn't have the capability to show dates as "mmm" and several other sheets rely on this one to be "mmm").

I've figured out these pieces:

1. Part 1: convert the months to numbers, e.g., Jan = 1, Feb = 2 and subtract: Feb - Jan or 2 - 1 then add +1 to get the correct number to populate Duration (months)---that's "2" for line 1 and "4" for line 2 in my example.
2. Part 2: Simply dividing the Total USD by the Duration gets me the $amount I spread across each month.
3. Part 3: Recognize the starting month and how many months duration to spread the $amount.

I feel like CONTAINS is in there but don't see how that will spread the $amount.

Any help would be super appreciated!

example.png

Paul Newcome

In the Jan column use

=IF(AND([Start Mnth#]@row <= 1, [End Mnth#]@row >= 1), [Total USD]@row / [Duration (months)]@row)

Then just change the 1 to a 2 for the Feb column, 3 for Mar, so on and so forth.

Tmorrell

OK, I figured out part 1 & 2: how to convert the mmm into a number so I can derive the number of months to divide the Total USD by:

• [Start mnth#] helper cell: =IF(Starts@row = "Jan", "1", IF(Starts@row = "Feb", "2", IF(Starts@row = "Mar", "3", IF(Starts@row = "Apr", "4", IF(Starts@row = "May", "5", IF(Starts@row = "Jun", "6", IF(Starts@row = "Jul", "7", IF(Starts@row = "Aug", "8", IF(Starts@row = "Sep", "9", IF(Starts@row = "Oct", "10", IF(Starts@row = "Nov", "11", IF(Starts@row = "Dec", "12"))))))))))))
• [End mnth#] helper cell: =IF(Ends@row = "Jan", "1", .........
• Duration is a simple formula (used VALUE to turn text "1" into a calculable number) then added a +1: =(VALUE([End mnth#]@row) - VALUE([Start mnth#]@row)) + 1

Now, is there a formula that would spread the Total USD divided by the Duration (months) starting at the correct month and across the number of columns?

Paul Newcome

In the Jan column use

=IF(AND([Start Mnth#]@row <= 1, [End Mnth#]@row >= 1), [Total USD]@row / [Duration (months)]@row)

Then just change the 1 to a 2 for the Feb column, 3 for Mar, so on and so forth.

Tmorrell

@Paul Newcome Brilliant and elegant! Thank you!!

Paul Newcome

Happy to help. 👍️

Christopher Avila

Hey quick question! So this formula works and applies the value to column "JAN" for me but does not apply it to any of these columns after such as FEB, MAR, APR. The example I am working on has a duration of 10 months however the value is only applying to month 1 (JAN). The other cells are blank.

https://community.smartsheet.com/discussion/comment/315536#Comment_315536

Christopher Avila

https://community.smartsheet.com/discussion/comment/315899#Comment_315899

Was actually able to figure this out. My next question here is regarding the screenshot. Why are October and November populating if the "end date" is in March?

Formula for Jan-Dec is: =IF(AND([Helper Start Date 2]@row <= 1, [Helper End Date 2]@row >= 1), [Total PO or Line]@row / Duration@row)

Jan=1 Feb=2......Oct=10 Nov=11 and so on. @Tmorrell did you experience this issue?

Tmorrell

Christopher, Paul is the real expert but if I understand correctly, his formula is based on the logic that Jan = 1, Feb = 2, Mar = 3 so it's checking if the Start mnth# is equal or LESS than the month number (e.g., Feb or 2) AND if the End mnth# is = or GREATER than the month number.

So, if a charge runs Feb to Apr that translates to "Start mnth#" 2 and "End mnth#" 4.

• Your Feb formula is =IF(AND([Start Mnth#]@row <= 2, [End Mnth#]@row >= 2), [Total USD]@row / [Duration (months)]@row) -- true, Start mnth# = 2 AND End mnth# is > 2 (it's 4).
• Your Mar formula is =IF(AND([Start Mnth#]@row <= 3, [End Mnth#]@row >= 3), [Total USD]@row / [Duration (months)]@row) -- true, 2 is < 3 AND 4 is > 3.
• Your Apr formula is =IF(AND([Start Mnth#]@row <= 4, [End Mnth#]@row >= 4), [Total USD]@row / [Duration (months)]@row) -- true, 2 is < 4 AND 4 is = to 4.
• Your May formula is =IF(AND([Start Mnth#]@row <= 5, [End Mnth#]@row >= 5), [Total USD]@row / [Duration (months)]@row) -- false, 2 is less than 4 but 4 is NOT => than 5.

Tmorrell

Yes, the issue is in my original helper formula:

• [Start mnth#] helper cell: =IF(Starts@row = "Jan", "1", IF(Starts@row = "Feb", "2", IF(Starts@row = "Mar", "3", IF(Starts@row = "Apr", "4", IF(Starts@row = "May", "5", IF(Starts@row = "Jun", "6", IF(Starts@row = "Jul", "7", IF(Starts@row = "Aug", "8", IF(Starts@row = "Sep", "9", IF(Starts@row = "Oct", "10", IF(Starts@row = "Nov", "11", IF(Starts@row = "Dec", "12"))))))))))))

Notice I'm translating the Jan into the "1" which is TEXT not a NUMBER. So you're Oct$ formula sees a "1" in 10 and believes it's true.

The fix is simple: In the formula above remove all the " " around the numbers so they are seen as numbers not a text character.

It also means for Duration you can ditch the "VALUE" piece since they are already numbers and don't need converting from text.

So, the final formulas look like this:

• Smart mnth# =IF(Starts@row = "Jan", 1, IF(Starts@row = "Feb", 2, IF(Starts@row = "Mar", 3, IF(Starts@row = "Apr", 4, IF(Starts@row = "May", 5, IF(Starts@row = "Jun", 6, IF(Starts@row = "Jul", 7, IF(Starts@row = "Aug", 8, IF(Starts@row = "Sep", 9, IF(Starts@row = "Oct", 10, IF(Starts@row = "Nov", 11, IF(Starts@row = "Dec", 12))))))))))))
• End mnth# =IF(Ends@row = "Jan", 1, IF(Ends@row = "Feb", 2, IF(Ends@row = "Mar", 3, IF(Ends@row = "Apr", 4, IF(Ends@row = "May", 5, IF(Ends@row = "Jun", 6, IF(Ends@row = "Jul", 7, IF(Ends@row = "Aug", 8, IF(Ends@row = "Sep", 9, IF(Ends@row = "Oct", 10, IF(Ends@row = "Nov", 11, IF(Ends@row = "Dec", 12))))))))))))))))))
• Duration #mnths =[End mnth#]@row - [Start mnth#]@row + 1
• Feb$ =IF(AND([Start mnth#]@row <= 2, [End mnth#]@row >= 2), [Total USD]@row / [Duration #mnths]@row)

Ryan D

How might @Paul Newcome 's formula be adjusted (or another person's) to account for partial months?

We are trying to track vendor contracts with specific start and end dates, and fixed fee.

Our bookkeepers want to know monthly cost projections.

The formulas above (esp Paul's very clean one) work well for full months, but if an end date slips just one day into the next month, the result is a full month's expense shows up.

I've tinkered with rounding, and tried to steal a 'median' formula from Excel, to no avail.

Hoping to avoid weekly helper columns.

Thank you for any pointers!