I'm trying to figure out the best way to calculate funding distribution over months/quarters. Based on provided contract start and end dates.
I'm already aware of the Smartsheet PRORATE function, however this distributes funding based on days (which vary by month/quarter). Large corporations calculate distributions my months and quarters.
Below is the excel formula we use to calculate funding by quarter. Unfortunately, Smartsheet does not have the DATEDIF or EDATE functions.
IFERROR(
DATEDIF(
MAX([quarter start date], DATE(YEAR([contract start date]), MONTH([contract start date]), 1)),
MIN([quarter end date], EDATE(DATE(YEAR([contract end date]), MONTH([contract end date]), 1), 1)),
"m")
/
DATEDIF(
DATE(YEAR([contract start date]), MONTH([contract start date]), 1),
EDATE(DATE(YEAR([contract end date]), MONTH([contract end date]), 1), 1),
"m"),
0)
* [total savings]
