I am looking for a formula that will count the number of Sundays within a
date range. Example: I want to enter a start date in one cell, and today's
date in another,and need a formula that will count the number of Sundays
between the two.A1 3/01/2008
A2 3/31/2008
A3 5 (5 Sundays between the dates)

If the two dates are:

Sunday, March 23, 2008
Sunday, March 30, 2008

does that count as two Sundays, one Sunday, or no Sundays??
--
Gary''s Student - gsnu200775


"Excelman" wrote:

> I am looking for a formula that will count the number of Sundays within a
> date range. Example: I want to enter a start date in one cell, and today's
> date in another,and need a formula that will count the number of Sundays
> between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)

{=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1)))=1,1,0))}

Do not type the outter brackets { } . This is an array formula committed by
Ctrl+Shift+Enter.

HTH,
Paul

--

"Excelman" <(E-Mail Removed)> wrote in message
news:B5C3E83D-F3BC-4156-AD54-(E-Mail Removed)...
>I am looking for a formula that will count the number of Sundays within a
> date range. Example: I want to enter a start date in one cell, and today's
> date in another,and need a formula that will count the number of Sundays
> between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)

Hi,

Srat date in a1
End date in b1

=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1,1,0))

Just for interest the =1 bit looks for Sundays 2 would be mondays etc

Mike

"Excelman" wrote:

> I am looking for a formula that will count the number of Sundays within a
> date range. Example: I want to enter a start date in one cell, and today's
> date in another,and need a formula that will count the number of Sundays
> between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)

good point

"Gary''s Student" wrote:

> If the two dates are:
>
> Sunday, March 23, 2008
> Sunday, March 30, 2008
>
> does that count as two Sundays, one Sunday, or no Sundays??
> --
> Gary''s Student - gsnu200775
>
>
> "Excelman" wrote:
>
> > I am looking for a formula that will count the number of Sundays within a
> > date range. Example: I want to enter a start date in one cell, and today's
> > date in another,and need a formula that will count the number of Sundays
> > between the two.A1 3/01/2008
> > A2 3/31/2008
> > A3 5 (5 Sundays between the dates)

With
A1: (the start date)
A2: (the end date)

This formula counts the number of Sundays within that range
=SUM(INT((WEEKDAY(A1-1)+A2-A1)/7))

Note: To count different weekdays, replace 1 with
one of these values: 1=Sun, 2=Mon.....7=Sat

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

Regards,

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

"Excelman" <(E-Mail Removed)> wrote in message
news:B5C3E83D-F3BC-4156-AD54-(E-Mail Removed)...
>I am looking for a formula that will count the number of Sundays within a
> date range. Example: I want to enter a start date in one cell, and today's
> date in another,and need a formula that will count the number of Sundays
> between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)

Check out this link... it has a pile of great date formulas...

http://www.cpearson.com/excel/DateTimeWS.htm
--
HTH...

Jim Thomlinson


"Excelman" wrote:

> I am looking for a formula that will count the number of Sundays within a
> date range. Example: I want to enter a start date in one cell, and today's
> date in another,and need a formula that will count the number of Sundays
> between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)

On Wed, 26 Mar 2008 14:26:03 -0700, Excelman
<(E-Mail Removed)> wrote:

>I am looking for a formula that will count the number of Sundays within a
>date range. Example: I want to enter a start date in one cell, and today's
>date in another,and need a formula that will count the number of Sundays
>between the two.A1 3/01/2008
> A2 3/31/2008
> A3 5 (5 Sundays between the dates)


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

--ron