Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob

Try the following...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)5))

....where A1 contains the start date and B1 contains the end date.

Hope this helps!

In article , "Rob"
wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob

Thankyou so much Domenic and so quick too!
I just need to amend it slightly as your formula does No. of week-end days
and not No. of week-ends.

Rob
"Domenic" wrote in message
...
Try the following...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)5))

...where A1 contains the start date and B1 contains the end date.

Hope this helps!

In article , "Rob"
wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob

Another way:
=SUMPRODUCT(--(WEEKDAY(A1:A31)=1),--(A1:A31B1),--(A1:A31<B2))+SUMPRODUCT(--(WEEKDAY(A1:A31)=7),--(A1:A31B1),--(A1:A31<B2))

(It should all be on one line)
I put the list of dates in A1:A31 and the between dates are stored in B1 and
B2.
For an explanation of SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj




"Rob" wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob

In article ,
"Rob" wrote:

Thankyou so much Domenic and so quick too!
I just need to amend it slightly as your formula does No. of week-end days
and not No. of week-ends.

I had wondered about that...obviously I guessed wrong...so here you
go... :-)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1-1)),2)=6),--(WEEKDAY(ROW(IN
DIRECT(A1+1&":"&B1)),2)=7))

Hope this helps!

On Mon, 20 Dec 2004 10:56:15 +1030, "Rob" wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob


If you have the Analysis Tool Pak installed, then:

=(A2-A1-networkdays(A1,A2)+1)/2

============================
If the NETWORKDAYS function is not available, and returns the #NAME? error,
install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.

--ron

Thanks again Domenic. In reflection, your first formula will actually do me
better, but thanks for the amendment as may help me with another issue.
Unfortunately, the environment this will be used in is at my office and they
don't use Excel. Strange but true! They use Quattro Pro....ugg! That
program uses all but the Indirect function. Is there another way to do this
without that function?
BTW it works just great in Excel
PS. I tried fiddling with a later post by pj but his formula comes up with
syntax error in Quattro. Nor can I use the Analysis Toolpak as suggested by
Ron

Rob

"Domenic" wrote in message
...
In article ,
"Rob" wrote:

Thankyou so much Domenic and so quick too!
I just need to amend it slightly as your formula does No. of week-end
days
and not No. of week-ends.

I had wondered about that...obviously I guessed wrong...so here you
go... :-)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1-1)),2)=6),--(WEEKDAY(ROW(IN
DIRECT(A1+1&":"&B1)),2)=7))

Hope this helps!

Thanks Ron. I'm sure this would work fine, but unfortunately I need to get
this working on a Quattro Pro spreadsheet. I was hoping for a formula I
could amend to suit and without having to install addins.

Rob

"Ron Rosenfeld" wrote in message
...
On Mon, 20 Dec 2004 10:56:15 +1030, "Rob" wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob


If you have the Analysis Tool Pak installed, then:

=(A2-A1-networkdays(A1,A2)+1)/2

============================
If the NETWORKDAYS function is not available, and returns the #NAME?
error,
install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then
click
OK.
If necessary, follow the instructions in the setup program.

--ron

Thanks tj, but I'm trying to make this formula work within a Quattro Pro
worksheet without success. Comes up with Syntax error. I'm not too
familiar with Quattro, but a lot of the functions are the same so I was
hoping for a formula that I could amend to suit that program.
I don't know where to go to get help for Quattro Pro users and as this
newsgroup is so good I hoped that someone here might be able to help. I
should have mentioned this at the start, eh!

Can someone help a sad Quattro user?

Rob

"tjtjjtjt" wrote in message
...
Another way:

=SUMPRODUCT(--(WEEKDAY(A1:A31)=1),--(A1:A31B1),--(A1:A31<B2))+SUMPRODUCT(--
(WEEKDAY(A1:A31)=7),--(A1:A31B1),--(A1:A31<B2))

(It should all be on one line)
I put the list of dates in A1:A31 and the between dates are stored in B1
and
B2.
For an explanation of SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

tj




"Rob" wrote:

Hi group,

Is there a formula that can extract the number of weekends between 2
variable dates?

Rob

Have you tried a Quattro Pro newsgroup? I've haven't had need to go to it,
but here is a place you could try:
http://support.corel.com/scripts/rig...p_faqid=754345

tj

"Rob" wrote:

Thanks again Domenic. In reflection, your first formula will actually do me
better, but thanks for the amendment as may help me with another issue.
Unfortunately, the environment this will be used in is at my office and they
don't use Excel. Strange but true! They use Quattro Pro....ugg! That
program uses all but the Indirect function. Is there another way to do this
without that function?
BTW it works just great in Excel
PS. I tried fiddling with a later post by pj but his formula comes up with
syntax error in Quattro. Nor can I use the Analysis Toolpak as suggested by
Ron

Rob

"Domenic" wrote in message
...
In article ,
"Rob" wrote:

Thankyou so much Domenic and so quick too!
I just need to amend it slightly as your formula does No. of week-end
days
and not No. of week-ends.

I had wondered about that...obviously I guessed wrong...so here you
go... :-)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1-1)),2)=6),--(WEEKDAY(ROW(IN
DIRECT(A1+1&":"&B1)),2)=7))

Hope this helps!

On Mon, 20 Dec 2004 18:34:40 +1030, "Rob" wrote:

Thanks Ron. I'm sure this would work fine, but unfortunately I need to get
this working on a Quattro Pro spreadsheet. I was hoping for a formula I
could amend to suit and without having to install addins.

Probably you'd get better advice from a QP news group.


--ron

In article ,
"Rob" wrote:

They use Quattro Pro....ugg! That
program uses all but the Indirect function. Is there another way to do this
without that function?

Not that I'm aware of...as others have suggested, you may want to seek
help in the Quattro Pro newsgroup.

Cheers!

Thanks tj. I didn't think there would be such a group. I'll try there.

Rob

"tjtjjtjt" wrote in message
...
Have you tried a Quattro Pro newsgroup? I've haven't had need to go to it,
but here is a place you could try:
http://support.corel.com/scripts/rig...p_faqid=754345

tj

"Rob" wrote:

Thanks again Domenic. In reflection, your first formula will actually do
me
better, but thanks for the amendment as may help me with another issue.
Unfortunately, the environment this will be used in is at my office and
they
don't use Excel. Strange but true! They use Quattro Pro....ugg! That
program uses all but the Indirect function. Is there another way to do
this
without that function?
BTW it works just great in Excel
PS. I tried fiddling with a later post by pj but his formula comes up
with
syntax error in Quattro. Nor can I use the Analysis Toolpak as suggested
by
Ron

Rob

"Domenic" wrote in message
...
In article ,
"Rob" wrote:

Thankyou so much Domenic and so quick too!
I just need to amend it slightly as your formula does No. of week-end
days
and not No. of week-ends.

I had wondered about that...obviously I guessed wrong...so here you
go... :-)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1-1)),2)=6),--(WEEKDAY(ROW(IN
DIRECT(A1+1&":"&B1)),2)=7))

Hope this helps!