Budgeting: Spread funding across Months/Quarter

procurementus

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]

dbertovich

Similar issue I am having. Wish to determine a year and a quarter an expense will be incurred in a format preferably as follows: 24Q2

jmyzk_cloudsmart_jp

Hi @procurementus

Your Excel formula translated into Smartsheet formula would be something like this;

IFERROR(
(MAX([Quarter Start Date]@row, DATE(YEAR([Contract Start Date]@row), MONTH([Contract Start Date]@row), 1)) - MIN([Quarter End Date]@row, DATE(YEAR([Contract End Date]@row), MONTH([Contract End Date]@row) + 1, 1) - 1) + 1) / (365/12)
/
((DATE(YEAR([Contract End Date]@row), MONTH([Contract End Date]@row) + 1, 1) - 1 - DATE(YEAR([Contract Start Date]@row), MONTH([Contract Start Date]@row), 1) + 1) / (365/12))

,0)

or