Budgeting: Spread funding across Months/Quarter

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]

Answers

  • 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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!