Difference - except for Saturdays and Sundays

[Rick]

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.

 

[David Biddulph]

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

 

[Rick]

Thanks David.

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

Mmmm, this will be a problem

Cheers,

 

[David Biddulph]

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

 

[Rick]

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

 

[David Biddulph]

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))

 

[Teethless mama]

This formula doesn't required Analysis Toolpak

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

Should of course be

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



--


Regards,


Peo Sjoblom

 

[Rick]

Magic! Thanks so much everyone.

Cheers, Ricky

 

[RichardSchollar]

Hello Peo

You can simplify that (very slightly) to:

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

Richard