Check for date range within date range

*updated, new attachment lower in post*

Hello all

I'm stuck with a logic that I can't figure out how to calculate with excel formulas.
I need to calculate the amount of full months a project occurs in the last, current and next financial year. I use DD/MM/YY date format.

Last financial year runs from 01/04/2022 until 31/03/2023
Current financial year runs from 01/04/2023 until 31/03/2024
Next financial year runs from 01/04/2024 until 31/03/2025

The solution need to be dynamic in such way that you can alter the dates of the last, current and next financial year, so the calculations will adapt to the new input. This file is to be used for several years.

I hope that the explanation above is sufficient to understand my question.
Anyone who knows which formula's to use to calculate the amount of months per financial year (date range)?

Thanx in advance.

Worksimplify

Please check your expected results. Some of them seem to be incorrect.

Exactly how do you want partial months to be treated?

Please check the dates for project D, which seems to involve time travel.

In D6, copied to full range.

Formula:

Please Login or Register  to view this content.

NOTE: I have changed year From 25 to 24 in C6 to suit the expected result in F6.