Formula Help: Allocate Money Across Fiscal Years (Based on Project Dates)
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

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

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.

Hi @
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.

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.

Thank you
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.

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
Categories
Check out the Formula Handbook template!