Solved

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

Posted on 2008-06-16
12
1,157 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

809 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