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
"T. Valko" wrote in message
...
Try this:
=4+(DAY(A1-DAY(A1)+35)<WEEKDAY(A1-DAY(A1)-DOW))
Where DOW = Mon - 1 thru Sun - 7
--
Biff
Microsoft Excel MVP
"Rick Rothstein (MVP - VB)" wrote in
message ...
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....