| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function for calculating workdays?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
Hi Ron
Is this function available in VBA code window? Regards TC, Sweden "Ron de Bruin" skrev i meddelandet ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... Hi Ron Is this function available in VBA code window? Regards TC, Sweden "Ron de Bruin" skrev i meddelandet ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
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 ToolsReferences, 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) "Thorleif Cederqvist" wrote in message ... Hi Ron Is this function available in VBA code window? Regards TC, Sweden "Ron de Bruin" skrev i meddelandet ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
Sorry I made a mistake
You must set the reference like Bob say -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ron de Bruin" wrote in message ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... Hi Ron Is this function available in VBA code window? Regards TC, Sweden "Ron de Bruin" skrev i meddelandet ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
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 -- Regards, Tom Ogilvy Bob Phillips wrote in message ... 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 ToolsReferences, 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) "Thorleif Cederqvist" wrote in message ... Hi Ron Is this function available in VBA code window? Regards TC, Sweden "Ron de Bruin" skrev i meddelandet ... 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 -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Thorleif Cederqvist" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function for calculating workdays?
On Sun, 12 Oct 2003 12:24:10 +0100, "Bob Phillips"
wrote: 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 ToolsReferences, 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(.....) 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating number of workdays between 2 dates | Excel Worksheet Functions | |||
| Calculating new date by entering number of additional workdays | Excel Worksheet Functions | |||
| Workdays Function | Excel Worksheet Functions | |||
| Calculating number of workdays | Excel Worksheet Functions | |||
| Workdays Function | Excel Worksheet Functions | |||
Linear Mode
