Calculating a total for remainder of month based on a monthly savings

MeganF

When the date is mid-month, I am missing $ amount for the partial month. Example below - the annual savings = EOY 2025.

Formula used in Annual Savings is: =IF(OR(ISBLANK([Savings Start Date]@row), ISBLANK([Monthly Savings]@row)), 0, (12 - MONTH([Savings Start Date]@row)) * VALUE([Monthly Savings]@row))

I have tried several options, but it's returning #Invalid Value

Lisa LS Kennedy

Hi Megan,

For the partial month you can use something like this:

=(30 - DAY([Savings Start Date]@row)) / 30 * [Monthly Savings]@row

or you can be even more accurate and calculate the last day of the month for any given date and then use that to create the partial month amount. Then adjust the 12 months to 11 if you want to include it in your original formula

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

Lisa LS Kennedy

Hi Megan,

For the partial month you can use something like this:

=(30 - DAY([Savings Start Date]@row)) / 30 * [Monthly Savings]@row

or you can be even more accurate and calculate the last day of the month for any given date and then use that to create the partial month amount. Then adjust the 12 months to 11 if you want to include it in your original formula

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com