Distributing Savings Across Months Based on Term Date and Fiscal Year

Trang Turtletraxx

Hello My Super Intelligent Smartsheet Community!

I have attempted to solve my problem by breaking it up into pieces and I'm stuck. I think I've been trying to solve this for too long and my creative juices are fried.

I have Service Providers we have contracts with. Each contract has a Contract Start and End Date and our monthly invoice amount for various aspects of the services they provide. This is allocated on our financial reports across each month evenly year over year.

If part of that service is decommissioned before the contract ends, we wanted to determine the amount of savings by ending the contract early. I was able to calculate the total remaining term months remaining in the contract and the monthly savings.

I then moved onto calculating savings based on our fiscal year (April thru March) calendar for the remainder of the current FY, the next FY and then the balance after that (if the savings spreads out that long)

Now I need to break this out into months along our fiscal calendar so I can determine how much total savings we accumulate from these decommissions each month and quarter. Can anyone help with suggestions how I can break up what I've calculated across a monthly calendar for each decommission?

For example, if you look at my screen shot, in the 1st row, that decommission has 7 months of savings this fiscal year. If I had a sheet with months across the top (2nd screen shot) Is there a formula that I can create that basically says, look at the Decomm Date of 06/09/25 and know that in the Apr thru June cells of the calendar would be nothing, but in the Jul, Aug, Sep, Oct, Nov, Dec, and Jan columns, the monthly savings should be displayed?

Is it even possible to do this with just a formula and without creating a million other helper columns? My gut says yes, but maybe after my brain has had a week's vacation :)

I sure hope that all made sense. I appreciate any time and effort in helping me solve this. You are always the best!🤩

Darren Mullen

@Trang Turtletraxx I don't know all the business logic, but assume that you are really just interested in the decommission month and year?

And since savings is strictly forward looking ( I assume) , you'd just check the decommission data to see if it's less than the month column and within the correct year based on the FY. So in your example for the June column you'd check if the MONTH() of the decommission date is < 6 and the year of the decom date is the appropriate year for the FY, then for the July column you'd check if the decommission date Month is < 7 and the FY, etc . If the month of the decommssion date is less than the month column, then you'd put a number in there.

Shooting from the hip here, but that's how I'd start approaching it. Trick is to track the correct actual year vs the fiscal year. Watch out for the wrap around years that March/April boundary. And the month numbers at the January/December wrap :)

Darren Mullen

@Trang Turtletraxx I don't know all the business logic, but assume that you are really just interested in the decommission month and year?

And since savings is strictly forward looking ( I assume) , you'd just check the decommission data to see if it's less than the month column and within the correct year based on the FY. So in your example for the June column you'd check if the MONTH() of the decommission date is < 6 and the year of the decom date is the appropriate year for the FY, then for the July column you'd check if the decommission date Month is < 7 and the FY, etc . If the month of the decommssion date is less than the month column, then you'd put a number in there.

Shooting from the hip here, but that's how I'd start approaching it. Trick is to track the correct actual year vs the fiscal year. Watch out for the wrap around years that March/April boundary. And the month numbers at the January/December wrap :)

Trang Turtletraxx

@Darren Mullen oh that's a good idea to create a formula for less than the month column, but you are right (and what I am always faced to find solution for) the cross between years within our FY will be tricky. Thanks so much!