Count the number of months between 2 dates but only in a defined period

Hi there,

I know I can use =DATEDIF(A1,B1,"m")+1 to calculate the months between 2 dates 01/05/2012 - 30/04/2013 =12.

However, I wanted to try and count the number of months between 2 dates but only if they fall in the stated time period e.g:-

01/01/2012 - 30/07/2014 but only the months between the time period from 01/05/2012 - 30/04/2013. So the answer in this case would be 12.

01/07/2012 - 30/06/2014 between the time period from 01/05/2012 - 30/04/2013. So the answer is 10.

I hope this makes sense and that someone can help me. It would be greatly appreciated.

Thank you.

Kind regards,

Forrest

That's exactly what I wanted thank you so much!

Regards,

Forrest

Glad it worked... please go back to the original post and mark the thread SOLVED...

Thanks for the rep, daddylonglegs...

Sugar, just one other problem that I have realised. Between the below dates the formula calculates as i wanted it to but because of the way i described what i want i now release this won't quite work because the calculation for the below would add upto 13. I need it to add upto 12. I can see why but do not know how to resolve.
01/01/2011 - 04/10/2011 =6
05/10/2011 - 30/04/2012 =7

I hope someone can help.

Regards,

Forrest

Try this...

=DATEDIF(IF(B1<DATE(2013,4,30),A1,MAX(A1,DATE(2012,5,1))),MIN(B1,DATE(2013,4,30)),"m")+1

it doesnt seem to work.

first calculates to 10 and the second to 7. I tried to amend the calculation to :-

=DATEDIF(IF(B1<DATE(2012,4,30),A1,MAX(A1,DATE(2011,5,1))),MIN(B1,DATE(2012,4,30)),"m")+1 but still couldnt get it to work.

Going back to the original formula... =DATEDIF(MAX(A1,DATE(2011,5,1)),MIN(B1,DATE(2012,4,30)),"m")+1

If you want the "fixed dates" to depend on the year on the first date, then maybe we can try this formula...
=DATEDIF(MAX(A1,DATE(YEAR(A1),5,1)),MIN(B1,DATE(YEAR(A1)+1,4,30)),"m")+1

Let me know what you think...

I am working it out in batches of financial years if that makes sense.

So:

FY12 = 01/05/2011 - 30/04/2012
FY13 = 01/05/2012 - 30/04/2013
FY14 = 01/05/2013 - 30/04/2014

and then

01/01/2011 04/10/2011
05/10/2011 30/04/2012

I am actually assessing for financial year FY12. But i would want the combination of the 2 differences to add up to 12 not 13. The first part you gave me worked great and i just needed to amend the years in the equation. I can see why it adds upto 13 because the end date of the first set of dates and the start dates of the second are in the same month but not sure if it is possible to stop this from happening. Thanks for all your help with this by the way. I thought it was a simplier question than what it has turned out to be.

Let's try this formula...
=ROUND((MIN(B1,DATE(YEAR(A1)+1,4,30))-MAX(A1,DATE(YEAR(A1),5,1)))/30,0)

Thank you so much I amended slightly to:-

=ROUND((MIN(J176,DATE(2012,4,30))-MAX(I176,DATE(2011,5,1)))/30,0)

and I will change for the batches. Once again thank you!

Very good... glad you're able to fix it to your liking...

It's not a perfect formula by any means, but it should get you there for the majority of the time... it will all depend on your dates, especially if they start creeping near the middle of the months... I know because I work with dates all the time and still have not perfected how to calculate months between two dates...

Let me know if you run into some problems...

=DATEDIF(MAX(D27,DATE(2015,1,1)),MIN(F27,DATE(2015,12,31)),"m")

Thank you this helped me