Solved

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

Posted on 2008-06-16
12
1,152 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Access application from Task Scheduler 6 34
Excel VBA - Returning results from batch file 9 67
DBF to ... Converter 5 43
File not loading into PowerPivot 4 9
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now