Calculating the workdays of the week

D

dcofam5

I need help calculating the workdays of the week.

If a task starts on a Thursday and lasts for four days, what is th
formula that will skip Sat. and Sun. (due to the fact that we don'
work those days) and return a value reflecting the project ends o
Tuesday?

Thanks for your help in advance,
Dav
 
F

Frank Kabel

Hi
have a look at
=NETWORKDAYS(A1,B1)
where A1 is the start day and B1 the end day

you may have to install the Analysis Toolpak Add-in
 
C

Chip Pearson

Dave,

Use the WORKDAY function. E.g.,

=WORKDAY(start_date, number_of_days)

The WORKDAY function is part of the Analysis Tool Pak add-in, so
you must have this loaded (Tools menu, Add-Ins) in order to use
the function; otherwise, you'll get a #NAME error.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
N

Norman Harker

Hi dcofam5!

To add 4 working days to a date use:

=WORKDAY(A1,4,Holidays)

Where A1 contains the date and the range named Holidays contains a
list of (excluded) holidays. The third argument is optional.

WORKDAY is an Analysis ToolPak function. If the above returns #NAME!
then Analysis ToolPak is not installed or selected (or you haven't
named a range Holidays).

Tools > Addins
Select Analysis ToolPak
OK

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
E

Erin

Beware that if you send the file to someone else who
doesn't have the Analysis Toolpak Add-in installed this
value will return an error when the other user views the
file.
 
A

amadeus068

Chip,

I am distributing my worksheet to various people. They may or may no
have ATP loaded or installed. How do I get around this?

Thanks in advance

Joe
 
D

Dave Peterson

How about just loading it in your Auto_Open code:

Option Explicit
Sub auto_open()

On Error Resume Next
Application.AddIns("Analysis ToolPak").Installed = True
If Err.Number = 0 Then
'do nothing, it's ok
Else
MsgBox "Analysis ToolPak Not loaded" & vbLf _
& "call Amadeus at: ####"
ThisWorkbook.Close savechanges:=False
End If
On Error Goto 0
End Sub

Comment out that .close line while you're testing. (or if you don't want to have
the workbook closed).

And depending on excel was installed, the user may be prompted (one time) to put
the distribution CD in (well, if excel was installed from a CD).

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top