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

[rasul]

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

 

[GorD]

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

 

[IML]

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.

 

[rasul]

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

 

[Aladin Akyurek]

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?

 

[IML]

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)

 

[rasul]

Thank you Aladin

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

 

[rasul]

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

 

[rasul]

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]

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.