No. of Saturdays between 2 dates
 

Hi All,

I want to count the number of Saturdays between 2 specifed dates.

i.e. between 1 Jan 08 and 31 Jan 08 there are 4 Saturdays.

Can anyone help please?

Thanks,

B/

No. of Saturdays between 2 dates
 

Change the range to suit your needs:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A29)),1)={1}))

Regards,
Ryan--

--
RyGuy


"Bhupinder Rayat" wrote:

Hi All,

I want to count the number of Saturdays between 2 specifed dates.

i.e. between 1 Jan 08 and 31 Jan 08 there are 4 Saturdays.

Can anyone help please?

Thanks,

B/

No. of Saturdays between 2 dates
 

Hi Ryan,

Thanks for this.

Is there anyway I can work out A1 and A29 dynamically based on 2 dates.

E.G

A1 - 1 Jan 08
A2 - 2 Jan 08
A3 - 3 Jan 08
A4 - 4 Jan 08
A5 - 5 Jan 08
A6 - 6 Jan 08
and so on.... to A29.

if in B1 i have 1 Jan 08 and in B2 i have 5 Jan 08, can I reference B1 & B2
to return A1 and A5 in the indirect function?

"ryguy7272" wrote:

Change the range to suit your needs:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A29)),1)={1}))

Regards,
Ryan--

--
RyGuy


"Bhupinder Rayat" wrote:

Hi All,

I want to count the number of Saturdays between 2 specifed dates.

i.e. between 1 Jan 08 and 31 Jan 08 there are 4 Saturdays.

Can anyone help please?

Thanks,

B/

No. of Saturdays between 2 dates
 

Hello,

See
http://www.sulprobil.com/html/date_formulas.html
please.

Regards,
Bernd

No. of Saturdays between 2 dates
 

ryguy7272 wrote...
Change the range to suit your needs:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A29)),1)={1}))
....

OP would also need to change the {1} to 7 to count Saturdays rather
than Sundays.

But there's no need to use INDIRECT. Longer, but not volatile,

=INT((A29-A1)/7)+OR(WEEKDAY(A1,1)WEEKDAY(A29,1),WEEKDAY(A29,1)= 7)

No. of Saturdays between 2 dates
 

On Mon, 14 Jan 2008 07:59:04 -0800, Bhupinder Rayat
wrote:

Hi All,

I want to count the number of Saturdays between 2 specifed dates.

i.e. between 1 Jan 08 and 31 Jan 08 there are 4 Saturdays.

Can anyone help please?

Thanks,

B/


In General:

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

where A2 is the end date; A1 is the start date, and DOW is the day of the week
with 1=Sun

So for Saturdays:

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

--ron