Calculate no. of Sundays in a month

[Guest]

Hi everybody,
Can we calculate no. of sundays from a given range of dates formatted as "d
mmm yy ddd"
for example
1 Nov 07 Thu
2 Nov 07 Fri
3 Nov 07 Sat
4 Nov 07 Sun
and so on....

 

[Ron Coderre]

Try this:

With Dates in A1:A10

This formula counts the number of Saturdays in that range:
=SUMPRODUCT(--(WEEKDAY(A1:A10)=7))

To count other days:
1=Sunday, 2=Monday,....7=Saturday

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Hi Ron,
Thanx for the help. It was very helpful

Try this:

With Dates in A1:A10

This formula counts the number of Saturdays in that range:
=SUMPRODUCT(--(WEEKDAY(A1:A10)=7))

To count other days:
1=Sunday, 2=Monday,....7=Saturday

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

I'm glad I could help........and thanks for the feedback

***********
Regards,
Ron

XL2003, WinXP

 

[Nacho]

What about if you wnat to know the no. of Sundays, but just from a single
cell, say

A1=3/1/2007

Thks

 

[joeu2004]

What about if you wnat to know the no. of Sundays, but just from a single
cell, say A1=3/1/2007

The following allows A1 to be any date within the month.

=SUMPRODUCT(--(WEEKDAY(EOMONTH(D9,-1)+ROW($A$1:INDIRECT("$A
$"&DAY(EOMONTH(D9,0)))))=1))

 

[joeu2004]

Errata....

The following allows A1 to be any date within the month.

=SUMPRODUCT(--(WEEKDAY(EOMONTH(D9,-1)+ROW($A$1:INDIRECT("$A
$"&DAY(EOMONTH(D9,0)))))=1))

Sigh, I had cut-and-pasted from a worksheet where I used D9 instead of
A1 for the date. The uninitiated might be confused by the ROW($A
$1:...) reference. So the correct formula for the question is:

=SUMPRODUCT(--(WEEKDAY(EOMONTH(A1,-1)+ROW($A$1:INDIRECT("$A
$"&DAY(EOMONTH(A1,0)))))=1))

 

[Rick Rothstein \(MVP - VB\)]

Here is a formula that, unlike joeu2004's offering, does not require the
Analysis ToolPak and is also not volatile...

=4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))>28))

Rick

 

[Sandy Mann]

Rick,

=4+SUMPRODUCT(--(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))>28))

Being as you are multiplying in the SUMPRODUCT() surely you don't need the
double unary?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

You are quite correct... it was left over from my testing, when I was trying
to get the individual sections to work correctly. Thanks for noticing that.

Although the original formula works correctly (the double unary, the
minus-minus signs, does nothing more than multiply by one), here is the
corrected formula for the archives...

=4+SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))=1)*(DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-ROW($1:$3))>28))

Rick

 

[T. Valko]

Try this:

=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW))

Where DOW = Mon - 1 thru Sun - 7

 

[Rick Rothstein \(MVP - VB\)]

Very nice!! I'm still trying to work out the math behind why your formula
works; but, being a programmer (we tend to favor zero-based series), I would
have said...

Where DOW = Sun - 0 thru Sat -6

And, while I recognize you were giving a general solution, I would note that
since the OP's question was for Sundays, your formula simplifies to this for
that condition...

=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)))

Rick

 

[Sunnyskies]

Afternoon from RSA,

Adding on to this, how would you change the formula to not just be for
Sundays but for all other days in the week.

A1=Mon
A2=Tues
A3=Wed
A4=Thur
A5=Fri
A6=Sat
A7=Sun

Thanks

 

[Ron Rosenfeld]

Afternoon from RSA,

Adding on to this, how would you change the formula to not just be for
Sundays but for all other days in the week.

A1=Mon
A2=Tues
A3=Wed
A4=Thur
A5=Fri
A6=Sat
A7=Sun

Thanks

To generalize, a formula giving the number of specific weekdays in the range
from date to another (i.e. inclusive of the starting and ending dates) can be
given by:

=INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)

where
A1: Start Date
A2: End Date
DOW: 1=Sunday; 2=Monday; etc
--ron

 

[Sunnyskies]

Thanks Ron, works well. So well that I suggest you go home. Tell your Boss
Uncle Bob says so.

Cheers

 

[Ron Rosenfeld]

Thanks Ron, works well. So well that I suggest you go home. Tell your Boss
Uncle Bob says so.

Cheers

It's so nice to have cheery rejoinders like yours. It gratifies me that yours
is the exception in these groups.
--ron