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....

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)

"Arup C" wrote in message
...
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....

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

"Ron Coderre" wrote:

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)

"Arup C" wrote in message
...
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....

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

A1=3/1/2007

Thks

"Ron Coderre" wrote:

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)

"Arup C" wrote in message
...
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....

On Jun 13, 2:34*pm, Nacho wrote:
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))

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


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

A1=3/1/2007

Thks

"Ron Coderre" wrote:

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)

"Arup C" wrote in message
...
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....

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


Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


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

A1=3/1/2007

Thks

"Ron Coderre" wrote:

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)

"Arup C" wrote in message
...
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....

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


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


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

A1=3/1/2007

Thks

"Ron Coderre" wrote:

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)

"Arup C" wrote in message
...
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....

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

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

XL2003, WinXP


"Arup C" wrote:

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....