# frequency of a particular month in a range of date say dec

#### rasul

##### New Member
how do I know the number of months that contain december between this range of date. 1/02/1995 to 1/07/2004.

cell A1= 1/02/1995
cell A2= 1/07/2004

cell A3= should contain a fomular to calculate how many 31st of December in the date range.

rasul

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Re: frequency of a particular month in a range of date say d

Not fully tested but for 31st's of December, does

=DATEDIF(A1,B1,"y")

not do it

Re: frequency of a particular month in a range of date say d

I think you need something like
=YEAR(A2)-YEAR(A1)+(MONTH(A2)=12)

a2 must be the later date.

It did not do it. But it will be helpful to know how many decembers in the range.
thank you

Re: frequency of a particular month in a range of date say d

With A1 <= A2, would you test:

=1+DATEDIF(A1,A2,"y")+(A2>DATE(YEAR(A2),12,1))

whether it fits the bill?

Re: frequency of a particular month in a range of date say d

A broader solution than the "easy" decemer 1. Generically:

=MAX((YEAR(B1)-1)-(YEAR(A1)+1),0)+(MONTH(A1)<=E1)+(MONTH(B1)>=E1)-(TEXT(A1,"m-yyyy")=TEXT(B1,"m-yyy"))

where e1 holds the number of the month you were counting (December was an easier month)

I got 10months instead of 9 months. Can you please explain the formula to me.

IML got the result but I do not understand the formula. I want to understand it so that if any of the variables change. I will be able to calculate it

thank you

IML

can we stick to your earlier formula. Now, will you be able to adjust it to calculate the number os septembers in the range

thank you

IML

can we stick to your earlier formula. Now, will you be able to adjust it to calculate the number os septembers in the range

thank you

Re: frequency of a particular month in a range of date say d

=MAX((YEAR(B1)-1)-(YEAR(A1)+1),0)+(MONTH(A1)<=E1)+(MONTH(B1)>=E1)-(TEXT(A1,"m-yyyy")=TEXT(B1,"m-yyy"))

=MAX((YEAR(B1)-1)-(YEAR(A1)+1),0)
counts the diffference in whole year. The max function disallows negatives.

+(MONTH(A1)<=E1) adds 1 if the month in the first year date starts at or before the month designated in e1

+(MONTH(B1)>=E1) adds 1 if the month in the second date starts at or after the month designiated in e1

-(TEXT(A1,"m-yyyy")=TEXT(B1,"m-yyy")) subtracts 1 in the case where the the two dates are in the same month and year, to avoid the duplciate counting that would happen with the previous two section.

Replies
3
Views
293
Replies
6
Views
225
Replies
4
Views
251
Replies
2
Views
162
Replies
18
Views
514

1,203,213
Messages
6,054,196
Members
444,708
Latest member
David R__

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back