Extracting number of months from date range based on specific criterea

**trianglehead** · 07-24-2013, 08:50 AM

hey guys

new to the forum and not sure it this is going to be in the correct section but, here we go.

I have a worksheet I'm currently working on to calculate time apportioned rates for corporation tax. I've done 99.9% of the work needed but as it stands I can't figure out how to get the date calculation to do what I need.

Basically what needs to happen is this.

The start and end date for the accounting period is entered and based on this I need to extract the number of months for 3 specific periods

example

start date = 01/03/2012
end date = 28/02/2013

from this I need to be able to extract the number of months that fall under:
x= 01/04/11-31/03/12
Y= 01/04/12-31/03/13
Z= 01/04/13-31/03/14

Is this at all possible and if so is anyone able to offer any assistance in getting this done?

Any pointers in the right direction would be greatly appreciated.

**daddylonglegs** · 07-24-2013, 09:22 AM

Hello trianglehead, welcome to Excel Forum,

will your accounting periods always be whole months (1st of month to last of another), if not how do you want to handle partial months?

For whole months try this:

list the start date of each x, y, z period in C1, D1 and E1

list your specific accounting period start date in A2, end date in B2 then use this formula in C2

=IFERROR(DATEDIF(MAX($A2,C$1),MIN($B2+1,EDATE(C$1,12)),"m"),0)

format as general and copy across to E2 - you can put more periods in subsequent rows and copy the formula down

**trianglehead** · 07-24-2013, 11:04 AM

Thanks for the quick response there! Much appreciated.

Just testing out the formula with the dates provided in my original post and it's only recognising period Y as having any month falling within the accounting period.

on paper the months split out as
x=1
y=9
z=2

the calculation needs to be whole months so, where possible rounding up/down to whole months would be necessary

**trianglehead** · 07-24-2013, 07:27 PM

Big thanks daddylonglegs

played around with the formula a little bit and finally got it to work the way it was needed. I'd forgotten that the Y and Z periods were different dates and lengths than the ones given.

Cheers for the help!