No of W/E between 2 dates

[Rob]

Hi group,

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

Rob

 

[Domenic]

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!

 

[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

 

[Guest]

Another way
=SUMPRODUCT(--(WEEKDAY(A1:A31)=1),--(A1:A31>B1),--(A1:A31<B2))+SUMPRODUCT(--(WEEKDAY(A1:A31)=7),--(A1:A31>B1),--(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

 

[Domenic]

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!

 

[Ron Rosenfeld]

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

 

[Rob]

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

 

[Rob]

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

 

[Rob]

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

 

[Guest]

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/rightnow.cfg/php.exe/enduser/std_adp.php?p_faqid=754345

tj

 

[Ron Rosenfeld]

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

 

[Domenic]

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!

 

[Rob]

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

Rob