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

DD04

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"?

Gia Thinh

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

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.

DD04

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

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

DD04

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

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.