Smart formula for dates required

[Nim2]

Hi everyone. This is my first post, so thanks in advance for any assistance. I need to create a formula and am completely stuck. I have the following data relating to various projects:

Start date - eg 1.5.2012
End date - eg 30.4.2016
project months - eg 48
total for project £ - eg 150000

What I need to do is calculate how many project months fall into each of our financial years. Our financial year runs from 1 Aug, so for the example above, I need a formula that calculates that 3 months of the project are in one financial year, 12 months in the next 3 years, and 9 months in the final year. I can do the calculation to work out what £ then fits to each financial year - it's identifying the number of months that I'm stuck on.

Can anyone help? I'm a bit of a doofus on Excel at the best of times, but this is completely beyond me!
Thanks
Nim2

[Pete_UK]

The problem with months is that they are of variable length, so if the start date is 15th Jan and the end date is 15th Feb in the same year, does this count as one month or two?

The attached file is based on one I put together last week. It doesn't show exactly what you want, but this one shows the number of days in each month that a date range covers - I've expanded it out to March 2017. Perhaps you can make use of this.

Hope this helps.

Pete

[Nim2]

Hi Pete
That's kind of what I want; I suspect I want a simplified version as I just need number of months (the numbers of days per month doesn't matter as the total £ is split evenly by number of months)by financial year.

Unfortunately your formula doesn't mean anything to me, but I'll take it home to my OH who is a bit more clued up on Excel - hopefully he can tweak it for me.

Thanks for your help.
Nim