Difference - except for Saturdays and Sundays
 

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.

Difference - except for Saturdays and Sundays
 

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.
--
David Biddulph

"Rick" wrote in message
...
Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.

Difference - except for Saturdays and Sundays
 

Thanks David.

Looks like I'll have to find the original Office CD.

Mmmm, this will be a problem :(

Cheers,


On Fri, 28 Dec 2007 12:37:26 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.

Difference - except for Saturdays and Sundays
 

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?
--
David Biddulph

"Rick" wrote in message
...
Thanks David.

Looks like I'll have to find the original Office CD.

Mmmm, this will be a problem :(

Cheers,


On Fri, 28 Dec 2007 12:37:26 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

=NETWORKDAYS(A1,A2)-1
which uses Analysis ToolPak.

Difference - except for Saturdays and Sundays
 

Nope, looks like it wasn't installed initially as when I select (tick)
it from the Tools/AddIns menu it asks for the Office CD.

Mind you, if I share the spreadsheet with other users, they might be
in the same predicament as me - no Toolpack installed on their PC.

Can this problem be resolved with a "normal" formula not requiring the
Toolpack?

Thanks, Ricky



On Fri, 28 Dec 2007 13:06:07 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?

Difference - except for Saturdays and Sundays
 

I haven't tested and analysed in great detail, but if your start and finish
dates are guaranteed not to be at weekends you might try
=A2-A1-2*INT((A2-A1)/7)-2*(MOD(A2-A1,7)WEEKDAY(A2,3))
--
David Biddulph

"Rick" wrote in message
...

Nope, looks like it wasn't installed initially as when I select (tick)
it from the Tools/AddIns menu it asks for the Office CD.

Mind you, if I share the spreadsheet with other users, they might be
in the same predicament as me - no Toolpack installed on their PC.

Can this problem be resolved with a "normal" formula not requiring the
Toolpack?

Thanks, Ricky



On Fri, 28 Dec 2007 13:06:07 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Usually the Analysis ToolPak is installed by default from the CD but not
enabled. Does it not appear on your Tools/ AddIns menu?

Difference - except for Saturdays and Sundays
 

This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.

Difference - except for Saturdays and Sundays
 

And this does not require volatile functions, courtesy Daniel Maher

=SUM(INT((B1-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Teethless mama" wrote in message
...
This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.

Difference - except for Saturdays and Sundays
 

Should of course be

=SUM(INT((A2-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Peo Sjoblom" wrote in message
...
And this does not require volatile functions, courtesy Daniel Maher

=SUM(INT((B1-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))



--


Regards,


Peo Sjoblom


"Teethless mama" wrote in
message ...
This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1


"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.

Difference - except for Saturdays and Sundays
 

Magic! Thanks so much everyone.

Cheers, Ricky

Difference - except for Saturdays and Sundays
 

Hello Peo

You can simplify that (very slightly) to:

=SUM(INT((WEEKDAY(A1-{2,3,4,5,6})-A1+A2)/7))

Richard


On Dec 28, 9:05 pm, "Peo Sjoblom" wrote:
Should of course be

=SUM(INT((A2-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))

--

Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message

...

And this does not require volatile functions, courtesy Daniel Maher

=SUM(INT((B1-WEEKDAY(A2+1-{2;3;4;5;6})-A1+8)/7))

--

Regards,

Peo Sjoblom

"Teethless mama" wrote in
...
This formula doesn't required Analysis Toolpak

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)<6))-1

"Rick" wrote:

Hi,

How can I get the difference in days between 2 dates, less any
Saturdays or Sundays that fall within the period, returned in A3?

e.g.

A1 = Start date of "5-Dec-07"
A2 = Finish date of "28-Dec-07"
A3 = Formula calculates days worked as "17"

Cell A3 would return 17 days, i.e. 23 less the 3 Saturdays and the 3
Sundays that fall within the priod.

Any help would be appreciated, Ricky.