Allocate months across periods in which they fall?

Hi there,

I have several hundred date ranges i.e. 1/1/09 - 1/1/11. I'm trying to allocate the number of months in each across the financial years ended 31/7/08 - 31/7/19 e.g. months in the financial year ended 31/7/09 would return 7 months and 31/7/12 onwards would return nil. The attached file should make it fairly obvious what I'm looking to achieve (hopefully)!

I've found the following thread - http://excel.bigresource.com/Track/excel-5l9CSSlj/ - which is trying to achieve the same as I am, except in terms of days/months rather than months/years - however, I've not had much luck modifying the formula for my own use. The first row in the attached document gives an idea of what I've done.

So - can anyone advise where I'm going wrong? Any help would be much appreciated!Example.xlsx

NB - now cross-posted to http://www.mrexcel.com/forum/showthr...14#post3210514. Any advice is still appreciated!

In C2 and copy right and down,

=MAX(0, MIN(C$1, $B2) - MAX(EDATE(C$1, -12), $A2))

That gives the number of days in each fiscal year. Convert to months as you wish.

The MONTH function gives the month of a given date (e.g., MONTH("7/26/2012") returns 7, for July). It does not count months.

The formula I posted gives days of overlap in a given month. I don't know what you are additionally trying to do.

Right. So there are 346 days of overlap for the fiscal year ending 7/31/10. How many months is that? You could divide by 30.4 (~ the average number of days in a month), but there is no exact answer.

You're welcome, good luck.