Not sure if you are looking for whole or part months but here's a quick(ish) way to go.
I set up a couple input cells for the start and end dates. I applied a named range for each just to tidy things up a bit.
I then set up a range of financial year start dates (col A) and in the column next to them (B) I put the following formula to calculate the days associated with the financial year starting on that date example formula incell B5):
The nested IFs do the following (in order):
- If the relative financial year starts after the end date then display nothing, otherwise:
- If the relative financial year ends before the start date then display nothing, otherwise:
- If the end date is before the end of the relative financial year the count the days from the start of this financial year to the end date (add 1 to include end date), otherwise:
- If the start date is before the start of the relative financial year then count the days in this f.year, otherwise:
- Find the days from the start date to the start of this financial year
This let me see that I was setting the logic for the ranges correctly.
I then changed the formula (in col C) to display whole months using DATEDIF in each of the result sections.
This is a bit limited as DATEDIF displays full calendar months. You might want to play with the days formula to get a truer representation of elapsed time.