weekdays per month

[ward kuipers]

Hi,

if I have a cell with a month (for example July 2004) and
I want to know how many mondays (or tuesdays, etc) are in
that specific month: How can I caculate this?

Thanx,
Ward Kuipers.

 

[Jason Morin]

One way:

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&DATE(YEAR
(A1),MONTH(A1)+1,1)-1)),"ddd")="Mon"))

This assumes "July 2004" is actually 7/1/2004 which is
what it should be if you just key in July 2004.

HTH
Jason
Atlanta, GA

 

[Daniel.M]

Hi Ward,

In you have the year number in A1 (2004) and the month number in A2 (7), the
numbers of Days of Week (DOW: where 1 for Sunday, 2 for Monday, ..., 7 for
Saturday) is given by :

=4+(DAY(DATE(A1,A2,35))<WEEKDAY(DATE(A1,A2,8-DoW)))

If you have a date (any date, as in July 27, 2004) in B2, the numbers of DoW in
the same month is given by :
=4+(DAY(B2-DAY(B2)+35)<WEEKDAY(B2-DAY(B2)+1-DoW))

In you always have the 1st of the month as a date, advise as the latter formula
could be abbreviated further.


Example:
For the number of Mondays in the same month as the date in B2
=4+(DAY(B2-DAY(B2)+35)<WEEKDAY(B2-DAY(B2)-1))

Regards,

Daniel M.

 

[Peo Sjoblom]

One way

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2004,7,1)&":"&DATE(2004,7+1,0))),2)
<6))

will count weekdays in July this year, if you want to put the Year in A1 and
the month (like in 7 for July) in A2

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(A1,A2,1)&":"&DATE(A1,A2+1,0))),2)<6
))

now for a particular week day start with 1 for Monday

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(A1,A2,1)&":"&DATE(A1,A2+1,0))),2)=1
))

shows there are 4 Mondays in July 2004

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Peter Atherton]

-----Original Message-----
Hi,

if I have a cell with a month (for example July 2004) and
I want to know how many mondays (or tuesdays, etc) are in
that specific month: How can I caculate this?

Thanx,
Ward Kuipers.
.

Ward

Not fully tested but thisseemsto be OK.

If Month Date is in H1 and day required is in J1 then

=IF(WEEKDAY(DATE(YEAR(H1),MONTH(H1),1))<=J1,INT((EOMONTH
(H1,0)-DATE(YEAR(H1),MONTH(H1),1)+1)/7),INT((EOMONTH(H1,0)-
DATE(YEAR(H1),MONTH(H1),1)+1)/7)+1)

Be careful of the wrapping.
J1 must be a number 1 for Sunday throughto 7 for Saturday.

(e-mail address removed)

Regards
Peter Atherton