Computing for year budget separately when start and end dates are across both years

anonymous user

Need help in computing for 2024 budget and 2025 budget respectively when start and end dates are across 2024 and 2025

Current formula is:

=IF(YEAR([Start date]@row) = 2024, IF(OR(ISBLANK([End date]@row), ISBLANK([Role Budget]@row)), "", ([Role Budget]@row * (((YEAR([End date]@row) - YEAR([Start date]@row)) * 12) + (MONTH([End date]@row) - MONTH([Start date]@row)) + 1 + IF(DAY([Start date]@row) > 23, -0.25, IF(DAY([Start date]@row) > 15, -0.5, IF(DAY([Start date]@row) > 7, -0.75, 0))) + IF(DAY([End date]@row) < 8, -0.75, IF(DAY([End date]@row) < 15, -0.5, IF(DAY([End date]@row) < 23, -0.25, 0)))))), "")

The problem is that for 2024 budget column, this is populating for both 2024 and 2025 data while for 2025 budget column, it's showing up as blank since start date is 2024

Michael Drayton

Hello Friend! You might be over thinking. If you budget for Start Dates only or End Dates only, you greatly simplify your formula because you open a pandora's box of considerations if you consider all the other possibilities. I like End Dates because they are more certain than Start Dates, at least in my world.

I also would create a helper column to extract Year and then calculate the financials in 2024 vs 2025. In this way, your formula doesn't have to do so much heavy lifting.

=YEAR([Date Column]@row )@row
=SUMIF([Year helper]:[Year helper], "2024", [budget column]:[budget column])

let me know if that helps.

Michael Drayton

Hello Friend! You might be over thinking. If you budget for Start Dates only or End Dates only, you greatly simplify your formula because you open a pandora's box of considerations if you consider all the other possibilities. I like End Dates because they are more certain than Start Dates, at least in my world.

I also would create a helper column to extract Year and then calculate the financials in 2024 vs 2025. In this way, your formula doesn't have to do so much heavy lifting.

=YEAR([Date Column]@row )@row
=SUMIF([Year helper]:[Year helper], "2024", [budget column]:[budget column])

let me know if that helps.

anonymous user

Hi Michael,

Thanks this was really helpful!