Microsoft, Excel, 2003, VBA - Creating a workday function

Hi CraigLowdon,

Why don't you use the function NETWORKDAYS() from the Analysis ToolPak add-in ?

thanks for reply,

I have enabled analysis toolpack but i dont see the option available under

application.worksheetfunction.*

also I would like this to be usable through variables internally within my program as well as on excel cells.

You would need to set a reference to the ATP or load the ATP VBA functions add-in.
If you want to create your own, do you have a list of bank holidays?
Regards,
Rory

i dont but i would be able to get them easily enough, and yes id prefer to be awkward and create my own function :-)

Function WorkingDays(dteStart As Date, dteEnd As Date) As Long
   Dim lngCounter As Long, dteTemp As Date
   dteTemp = dteStart
   Do
      Select Case Weekday(dteTemp, vbSunday)
         Case 2 To 6
            If Not IsHoliday(dteTemp) Then lngCounter = lngCounter + 1
         Case Else
      End Select
      dteTemp = dteTemp + 1
   Loop Until dteTemp > dteEnd
   WorkingDays = lngCounter
End Function
Function IsHoliday(dteCheck As Date) As Boolean
   Dim adteHolidays, varMatch
   adteHolidays = Array(39448, 39450)
   varMatch = Application.Match(CDbl(dteCheck), adteHolidays, 0)
   IsHoliday = Not IsError(varMatch)
End Function

The IsHoliday function has 1/1/2008 and 3/1/2008 entered as holidays for testing - adjust as required.

Regards,
Rory

Thanks for help Rory I understand the majority of that, the isholiday function is escaping me a little could you please explain how you have used that array and application feature? also is there any way to do this without using an excel feature as im thinking i may adapt this into other applications.

PS. i am using it to try to find how many non weekdays there are so I can take them out of the equation but im sure i can adapt the code for that myself.

Thanks again Rory.

I also think i am still having date issues as I have tried the following test and got 0 days.

MsgBox WorkingDays(11 / 6 / 2008, 16 / 6 / 2008)

Select allOpen in new window

Hi, Ive amended the code to do what i want it to do (i actually wanted to find how many weekend days were inbetween the workingdays but i didnt make that clear)
I then started on a case statement for the isholiday function but I then realised that I will need to change this every year by doing it this method.
is there any way to select a date in a year which is generic to any year?

Thanks again

Function NonWorkingDays(dteStart As Date, dteEnd As Date) As Long
   Dim WeekendCount As Long, dteTemp As Date
   dteTemp = dteStart
   Do
        Select Case Weekday(dteTemp, vbSunday)
            Case 1, 7
                WeekendCount = WeekendCount + 1
            Case Else
                If IsHoliday(dteTemp) = True Then WeekendCount = WeekendCount + 1
      End Select
      dteTemp = dteTemp + 1
   Loop Until dteTemp > dteEnd
   NonWorkingDays = WeekendCount
End Function
 
Function IsHoliday(dteCheck As Date) As Boolean
    
End Function

Select allOpen in new window

You could use an array of dates in dd-mmm format and then simply format each date similarly before checking it against the array?

Thanks for your help again Rory, I have opted to use a case statement with all the known static bank holidays this year which I will amend next year. :-) help greatly appreciated