?
Solved

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

Posted on 2008-06-16
12
Medium Priority
?
1,171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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 …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

777 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