Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

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

Posted on 2008-06-16
12
Medium Priority
?
1,174 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

647 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