Formula Help: Allocate Money Across Fiscal Years (Based on Project Dates)

Options

I am in search of a formula that will allow me to divide a set amount of funding based on how many months of the project term fall within a given fiscal year.

I've historically been doing this manually, by counting out the number of months myself, and writing out the formula for each cell.

Is there a formula that would automatically calculate the highlighted cells, if I filled out "start", "end" and "amount"?

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓
    Options

    I'm glad to hear it works well for you.

    Yes, the formula does exactly as you explained. I just want to clarify a bit about the IF check: it ensures the End date falls within FY27 before calculating the amount; otherwise, it skips the calculation.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Options

    Hi DD04,

    Assuming that your Start and End date are in the range of FY25 and FY26, so there's no need to validate them.

    In the Sheet Summary, create the FY25End field and try these formulas below in the FY25 and FY26 columns:

    FY25 : =IF(End@row <= [FY25End]#, (End@row - Start@row) / 30, ([FY25End]# - Start@row) / 30) * Amount@row / ((End@row - Start@row) / 30)

    FY26 : =Amount@row - [FY25]@row

    Hope it works for you.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • DD04
    DD04
    edited 06/18/24
    Options

    Hi @ https://community.smartsheet.com/profile/Gia%20Thinh

    Thank you very much for your help - The formula worked when I replicated it in my sheet for FY25.

    While this is helpful, I'm in search of a formula that could be copied into future fiscal years (FY26, 27, 28, 29, 30, and so on). As more future funding is secured, there will be start and end dates that fall within these future fiscal years.

    I appreciate your help.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Options

    I just modified the formulas and make them more generic for your use case.

    Here is the result.

    FY25 : =IF((MIN(End@row, [FY25End]#) - MAX(Start@row, [FY25Start]#) + 1) > 0, (MIN(End@row, [FY25End]#) - MAX(Start@row, [FY25Start]#) + 1) * Amount@row / ((End@row - Start@row + 1)))

    FY26 : =IF((MIN(End@row, [FY26End]#) - MAX(Start@row, [FY26Start]#) + 1) > 0, (MIN(End@row, [FY26End]#) - MAX(Start@row, [FY26Start]#) + 1) * Amount@row / ((End@row - Start@row + 1)))

    FY27: =IF((MIN(End@row, [FY27End]#) - MAX(Start@row, [FY27Start]#) + 1) > 0, (MIN(End@row, [FY27End]#) - MAX(Start@row, [FY27Start]#) + 1) * Amount@row / ((End@row - Start@row + 1)))

    The above formulas have the same format, the most diferent thing is the referenced summary fields FYxStart and FYxEnd dates


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

  • DD04
    Options

    Thank you https://community.smartsheet.com/profile/Gia%20Thinh

    This works wonderfully. Since I do not understand how the formula works, I'm wondering if you would be able to tell what the formula is doing, exactly?

    Is it determining the number of days between "Start" and "End" that fall within a given FY, and then dividing by the total number of days between "Start" and "End"? And then taking that proportion and multiplying by the "amount"?

    I very much appreciate the help you have provided me so far.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓
    Options

    I'm glad to hear it works well for you.

    Yes, the formula does exactly as you explained. I just want to clarify a bit about the IF check: it ensures the End date falls within FY27 before calculating the amount; otherwise, it skips the calculation.


    Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!