No of W/E between 2 dates

R

Rob

Hi group,

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

Rob
 
D

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!
 
R

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
 
T

tjtjjtjt

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
 
D

Domenic

Rob said:
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.
Click to expand...

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!
 
R

Ron Rosenfeld

Hi group,

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

Rob
Click to expand...

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
 
R

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
 
R

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
 
R

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
 
R

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.
Click to expand...

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


--ron
 
D

Domenic

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

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

Cheers!
 
Top