Counting first of the month


Posted by Christiaan Verdoold on August 08, 2001 2:54 PM

What is the shortest formula to count how many times there has been a first of the month in a period? For example: in the period of 03/06/01 to 08/08/01 there has been 2 times a first of the month 1-7-01 and 1-8-01. How can i translate that in a formula?

thanx
chris

Posted by Aladin Akyurek on August 08, 2001 2:59 PM

Use either of:

(1) =SUMPRODUCT((DAY(A1:A100)=1)+0)

(2) =SUM((DAY(A1:A100)=1)+0) [ This one must be array-entered; That is, you need to hit CONTROL+SHIFT+ENTER at the same time (instead of just ENTER) to enter it. ]


Aladin

Posted by Barrie Davidson on August 08, 2001 3:01 PM

How about (assuming the first date is in A1 and the second date is in B1):

=(YEAR(B1)-YEAR(A1))*12+IF(DAY(A1)=1,(MONTH(B1)-MONTH(A1))+1,(MONTH(B1)-MONTH(A1)))

Regards,
Barrie

Posted by Aladin Akyurek on August 08, 2001 3:07 PM

(1a) =SUMPRODUCT((A1:A100>=B1)*(A1:A100<=B2)*(DAY(A1:A100)=1))

if interested in the number of day 1's between 2 dates, the smaller in B1 and the larger in B2

(2a) Modify along the lines of 1a.




Posted by Aladin Akyurek on August 08, 2001 3:15 PM

I reckon yours is the answer. Mine tackles with something related but different (a case of too fast reading).

Aladin :(