Function for calculating workdays?

  • Thread starter Thread starter Thorleif Cederqvist
  • Start date Start date
T

Thorleif Cederqvist

Hi

Is there a function to calculate workdays? What I have is two serial
numbers. The period between them also includes weekends, that should be
eliminated.

Thanks in advance
TC, Sweden
 
Check out this function

NETWORKDAYS(start_date,end_date,holidays)

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.
Syntax
 
Hi Ron

Is this function available in VBA code window?

Regards
TC, Sweden
Ron de Bruin said:
Check out this function

NETWORKDAYS(start_date,end_date,holidays)

If this function is not available, and returns the #NAME? error, install
Click to expand...
and load the Analysis ToolPak add-in.
How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.
Syntax





--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"Thorleif Cederqvist" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Yes

Sub test()
Dim workdaycount As Integer
workdaycount = NETWORKDAYS(Date, Date + 14)
MsgBox workdaycount
End Sub

Check the VBA Addin also On the Tools menu
 
Thorleif,

NETWORKDAYS is part of the Analysis Toolpak addin, and as such you cannot
use it in VBA as you would the built-in functions.

However, it is still possible to use it. In VBA, go to Tools>References, and
check the ATP library. In English it's called ATPVBAEN.XLS.

Then you call the function as for any other function, such as
Msgbox NetWorkdays(.....)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Just an added comment:
You probably won't see it (ATPVBAEN.XLS - "EN" meaning English I assume) in
the Tools=>Registry in the Visual Basic Editor unless you have it loaded
under tools=>addins in the regular Excel window. You would select

Analysis Toolpak - VBA
 
Thorleif,

NETWORKDAYS is part of the Analysis Toolpak addin, and as such you cannot
use it in VBA as you would the built-in functions.

However, it is still possible to use it. In VBA, go to Tools>References, and
check the ATP library. In English it's called ATPVBAEN.XLS.

Then you call the function as for any other function, such as
Msgbox NetWorkdays(.....)
Click to expand...

One further comment. The ATP function WORKDAY is very US-centric with regard
to date interpretation. It may be that NETWORKDAYS is also US-centric. If
that is the case, and TC is not using the US date format, NETWORKDAYS may not
function properly for him. I recall a French poster having a date related
problem with the WORKDAY function, and we wound up writing a VBA routine that
did not involve the ATP.


--ron
 
Back
Top