Solved

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

Posted on 2008-06-16
12
1,160 Views
Last Modified: 2010-04-21
Hi, i need to create a function that I can use in a vb macro i am creating or in any future macros.

the format needs to be WORKINGDAYS(Startdate,EndDate) and it needs to take into consideration bank holidays.

this seems relatively straight forward but i dont know the vb commands to recognise which number is a saturday or sunday, also the formats of my dates just seem to return 0 all the time and I dont know how to make it recognise the dates properly.

example: 16/06/2008 - 12/06/2008 = 0 when i would expect this to equal 4

Any help greatly appreciated.
Thanks
Craig
0
Comment
Question by:CraigLowdon
  • 7
  • 4
12 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 21794617
Hi CraigLowdon,

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

Author Comment

by:CraigLowdon
ID: 21794693
thanks for reply,

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

application.worksheetfunction.*

0
 

Author Comment

by:CraigLowdon
ID: 21794704
also I would like this to be usable through variables internally within my program as well as on excel cells.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21794829
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
0
 

Author Comment

by:CraigLowdon
ID: 21795089
i dont but i would be able to get them easily enough, and yes id prefer to be awkward and create my own function :-)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21795235
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
0
 

Author Comment

by:CraigLowdon
ID: 21795364
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.
0
 

Author Comment

by:CraigLowdon
ID: 21795380
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)

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 21796431
The WorkingDays function will exclude weekdays - it only looks at Monday to Friday, then checks them for holidays (since you're not interested in weekends anyway, there is no point checking those). If you want to do it without using Excel functions, I think you will need a date array and then loop through checking those against the date in question.
WorkingDays(11 / 6 / 2008, 16 / 6 / 2008)
should be something like:
WorkingDays(CDate("11 / 6 / 2008"), CDate("16 / 6 / 2008"))

Regards,
Rory
0
 

Author Comment

by:CraigLowdon
ID: 21801545
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 21801584
You could use an array of dates in dd-mmm format and then simply format each date similarly before checking it against the array?
0
 

Author Closing Comment

by:CraigLowdon
ID: 31467649
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
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question