Counting a day of the week

N Harkawat · 05-25-2005, 04:06 PM

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=6))
where cell a1 holds the date 6/14/05 and b1 holds 6/29/05

"L_n_da" <L_n_da@discussions.microsoft.com> wrote in message
news:3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com...
>I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd
> be
> very grateful for your help.

L_n_da · 05-25-2005, 04:06 PM

I need to count the number of Fridays in a range of dates, e.g. how many
Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
very grateful for your help.

Ron Rosenfeld · 05-25-2005, 05:06 PM

On Wed, 25 May 2005 12:43:25 -0700, L_n_da <L_n_da@discussions.microsoft.com>
wrote:

>I need to count the number of Fridays in a range of dates, e.g. how many
>Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
>very grateful for your help.

A1: Start Date
A2: End Date

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

In general:

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

where DOW is replaced by the Day of the Week where Sunday=1 and Saturday=7.

--ron

Gnasher · 08-30-2005, 07:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-05-2005, 10:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-05-2005, 11:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 12:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 01:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 02:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 03:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 04:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 05:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 06:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 07:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 08:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 09:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 10:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 11:05 AM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 12:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 02:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 03:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 04:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 05:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 06:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 07:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Gnasher · 09-06-2005, 09:05 PM

In article <3CC6B1CE-CE9D-4162-AB0A-351F95EE18D9@microsoft.com>,
L_n_da@discussions.microsoft.com says...
> I need to count the number of Fridays in a range of dates, e.g. how many
> Fridays between 14 June 05 and 29 June 05 (including these two days). I'd be
> very grateful for your help.
>
=INT((Finish-Start)/7)+IF(OR(WEEKDAY(Start-6)>WEEKDAY(Finish-6),WEEKDAY
(Finish)=6),1,0)

seems to work!

Counting a day of the week

LinkBack

Thread Tools

Rate This Thread

Display

Re: Counting a day of the week