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 :(
