Difference - except for Saturdays and Sundays

R

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

Rick

Thanks David.

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

Mmmm, this will be a problem :(

Cheers,
 
D

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

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
 
D

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

Teethless mama

This formula doesn't required Analysis Toolpak

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

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
 
P

Peo Sjoblom

Should of course be

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



--


Regards,


Peo Sjoblom
 
R

RichardSchollar

Hello Peo

You can simplify that (very slightly) to:

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

Richard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top