weekdays per month

  • Thread starter Thread starter ward kuipers
  • Start date Start date
W

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.
 
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
 
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.
 
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)
 
-----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.
.
Click to expand...
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
 
Back
Top