Count days excluding Weekends

[Bill]

I have a worksheet that shows when equipment is picked up
for repair.
The Service Level Agreement (SLA) states equipment should
be returned within 5 days but this excludes weekends and
public holidays.
if equipment leaves on a Friday to the repair station,
the SLA time means it must be back by the following
Friday (excludes the intervening Sat/Sun)
Tall order - is it possible to create a formula that
checks the system calender and then posts the return due
date in the cell
Will I have to create a working day calender for this?
Bill

 

[Frank Kabel]

Hi
use the function
WORKDAY. e.g.
=WORKDAY(A1,5)

Note: The Analysis Toolpak Addin must be installed for this

 

[Gary Thomson]

Hi Bill,

You need to use a function called WORKDAY. This function
returns a number that represents a date that is the
indicated number of working days before or after a date
(the starting date). Working days exclude weekends and any
dates identified as holidays.

To do it you need to enter the following:

Assuming your date that the equipment leaves for the
repair station is in cell A1, enter the following in B1
(or wherever you like):

=WORKDAY(A1,5)

This will return the date that is 5 WORKING days after the
date in A1.

Note that working day only excludes weekends in the above
formula. But it is possible to have holidays (i.e.
Christmas day, New Year Day) excluded as well.

To do this, enter all the dates that are holidays in the
forthcoming year (not including weekends) in, say, Sheet2!
A1:A100. Now change your formula above to:

=WORKDAY(A1,5,Sheet2!A1:A100)

Now, if, say, we included in A1:A100 17 September 2004 as
a holiday, then the formula above would return 20-
September 2004 if the date the goods left for repair was
10-September 2004.

Happy to Help,

Gary Thomson