Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Business Day Calculation

    Dear Seniors,

    I got the following code from internet and I am using the same for my project for Working days calculation. It works fine but I have a minor problem, could you please help me to sort out this issue.

    Code:
    Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
    On Error GoTo Error_Handler
    
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    'Dim strSQL As String
    
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM 10_Holidays", dbOpenSnapshot)
    
    If intDayAdd > 0 Then
        Do While intDayAdd > 0
            datStart = datStart + 1
            rst.FindFirst "[HolidayDate] = #" & datStart & "#"
            If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
                If rst.NoMatch Then intDayAdd = intDayAdd - 1
            End If
        Loop
        
    ElseIf intDayAdd < 0 Then
    
        Do While intDayAdd < 0
            datStart = datStart - 1
            rst.FindFirst "[HolidayDate] = #" & datStart & "#"
            If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
                If rst.NoMatch Then intDayAdd = intDayAdd + 1
            End If
        Loop
        
    End If
    
        GetBusinessDay = datStart
    
    Exit_Here:
        rst.Close
        Set rst = Nothing
        Set DB = Nothing
        Exit Function
        
    Error_Handler:
        MsgBox Err.Number & ": " & Err.Description
        Resume Exit_Here
    End Function
    Now the problem is when the end date is a holiday then it should show the next working day in the result, however this code returns me the wrong result (i.e) HolidayDate (it is declared as Holiday in my Table).

    Could you please help me to correct the code in order to get the correct result.

    Also I would like to clarify the following, I need to give 15 working days for client review not considering the start date. However In my formula I am using 16 to get the result, I would like to understand why I need to give 16?

    Following is the formula I am using in my query

    Deadline: IIf(TimeValue([IssuedDate])>#9:30:00 AM#,GetBusinessDay([IssuedDate],17),GetBusinessDay([IssuedDate],16))

    For ex I had submitted the document to CPY 06-May-14, 2:00 PM. From this 15 Working days will come to 30-May-14 (Since 15,27 & 29 are Holidays). However my result shows 29-May-14 which is a holiday.

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Instead of scrolling thru EVERY record simply Dcount the # of holidays between the 2 dates.
    And Dcount the # of work days.
    View this:

    Code:
    Public Function Workdays(ByRef pvStartDate As Date) As Integer
    
        ' Returns the number of workdays between startDate
        ' and endDate inclusive.  Workdays excludes weekends and
        ' holidays. Optionally, pass this function the name of a table
        ' or query as the third argument. If you don't the default
        ' is "Holidays".
        On Error GoTo Workdays_Error
        Dim nWeekdays As Integer
        Dim nHolidays As Integer
        Dim strWhere As String
        Dim vStartDate, vEndDate
        
         'add 16 days to the start date
        vStartDate = pvStartDate
        vEndDate = DateAdd("d", 16, pvStartDate)
        
        nWeekdays = Weekdays(vStartDate, vEndDate)
        If nWeekdays = -1 Then
            Workdays = -1
            GoTo Workdays_Exit
        End If
        
        ' Count the number of holidays.
        strWhere = "[Holiday] >= #" & vStartDate & "# AND [Holiday] <= #" & vEndDate & "#"
        nHolidays = DCount("*", "[tHolidays]", strWhere)
        
        Workdays = nWeekdays + nHolidays     'add the days missed due to holidays
        
    Workdays_Exit:
        Exit Function
        
    Workdays_Error:
        Workdays = -1
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Workdays"
        Resume Workdays_Exit
        'Weekdays(vStartDate, vEndDate)
    End Function
    
    Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
        ' Returns the number of weekdays in the period from startDate
        ' to endDate inclusive. Returns -1 if an error occurs.
        ' If your weekend days do not include Saturday and Sunday and
        ' do not total two per week in number, this function will
        ' require modification.
        On Error GoTo Weekdays_Error
        
        ' The number of weekend days per week.
        Const ncNumberOfWeekendDays As Integer = 2
        
        ' The number of days inclusive.
        Dim varDays As Variant
        
        ' The number of weekend days.
        Dim varWeekendDays As Variant
        
        ' Temporary storage for datetime.
        Dim dtmX As Date
        
        ' If the end date is earlier, swap the dates.
        If endDate < startDate Then
            dtmX = startDate
            startDate = endDate
            endDate = dtmX
        End If
        
        ' Calculate the number of days inclusive (+ 1 is to add back startDate).
        varDays = DateDiff(Interval:="d", _
            date1:=startDate, _
            date2:=endDate) + 1
        
        ' Calculate the number of weekend days.
        varWeekendDays = (DateDiff(Interval:="ww", _
            date1:=startDate, _
            date2:=endDate) _
            * ncNumberOfWeekendDays) _
            + IIf(DatePart(Interval:="w", _
            Date:=startDate) = vbSunday, 1, 0) _
            + IIf(DatePart(Interval:="w", _
            Date:=endDate) = vbSaturday, 1, 0)
        
        ' Calculate the number of weekdays.
        Weekdays = (varDays - varWeekendDays)
        
    Weekdays_Exit:
        Exit Function
        
    Weekdays_Error:
        Weekdays = -1
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
            vbCritical, "Weekdays"
        Resume Weekdays_Exit
    End Function

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Ranman256,

    Thanks for your post. My objective is to calculate the End Date not the no of Workdays. (i.e.) My input will be Start Date, No of Days for review. Based on this input I need to calculate the End Date. My code works fine, however I have a problem when the end date is a holiday then it should show the result of next working day.

    Remaining things are fine with my code.

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure that you'll have problems if there is a holiday anywhere between the start and end dates.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I work for a local branch of a multinational company and we have partners (other branches of the company, customers, suppliers, ...) almost everywhere in the world.

    - All these partners are identified in a table having a column that specifies a localisation in an area (Europe, Japan, USA, Middle East, Mexico, South America, China, Thailand, Indonesia, South Africa, New Zealand).
    - All tables involved in date computations (orders, deliveries, etc.) have a foreign key referencing the Partners table.
    - We use a calendar table with a set of attributes that specifies the "status" of each day for one of the defined areas (First day of week, holiday, etc.). Actually, the system is more complex and does not depend on a fixed number of areas, but you get the general idea.
    -With this system, it's easy to use the calendar table to retrieve the number of workdays between 2 dates or the first working day x days from a given date for a given area.

    Of course, you have enter the lists of holidays and other calendar specific information into the system but there are web services that provide such lists, so the task is not too hard.
    Have a nice day!

  6. #6
    Join Date
    Oct 2013
    Posts
    163
    Hi PatP,

    Quote Originally Posted by Pat Phelan View Post
    I'm pretty sure that you'll have problems if there is a holiday anywhere between the start and end dates.
    Thanks for your reply. I was using this code for last 2 months and it was working fine until last week. So I thought everything is working fine but unfortunately I get this error now.

    What is strange for me is If the end date is a Saturday or Sunday then it shows the next Working Date (Monday). However If the end date is a Holiday as defined by me the Table it does not shows the next working day. I would like to get the help from this forum to put a condition to check end date is a holiday, if it is then it should show the next working date.

    Thanks and Regards
    R. Vadivelan

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Can I get the link where I can see how it works or a demo database?

    Or Is there any other code available to calculate the end date?

    Because I was searching in google for last 3 days and I got 3 set of codings, but everything differs by its coding structure/conditions. I would like to get a simple code which will sort out my issue.

    Please help me.

    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    Can I get the link where I can see how it works or a demo database?
    It's a corporate feature that's not in the public domain. I cited it as an example of what can be done.

    As for creating a calendar table, there are several solutions. See for instance: T-SQL: Calendar Table - TechNet Articles - United States (English) - TechNet Wiki or SQL-create Calendar Table. The topic concerns SQL Server but it's easily transposable in Access.
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your post. I had sorted out my issue with help of your previous post in this forum. I had just formated the Date and my code is showing the correct result.

    But I would like to have your assistance for creating the following formula into user defined function

    My Formula is

    Deadline1: IIf(TimeValue([IssuedDate])>#10:00:00 AM#,GetBusinessDay(Format([IssuedDate],'dd-mmm-yy'),15),GetBusinessDay(Format([IssuedDate],'dd-mmm-yy'),14))

    I would like to convert this formula into userdefined function. I am too confused in conversion and getting errors, because GetBusinessDay is a another function.
    Thanks and Regards
    R. Vadivelan

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where's the problem?
    Code:
    Public Function Deadline(ByVal IssuedDate As Variant) As Variant
    
        Deadline = IIf(TimeValue(IssuedDate) > #10:00:00 AM#, _
                       GetBusinessDay(Format(IssuedDate, "dd-mmm-yy"), 15), _
                       GetBusinessDay(Format(IssuedDate, "dd-mmm-yy"), 14))
                  
    End Function
    Note: This requires that the function GetBusinessDay() accepts a parameter of type String or Variant (because of the Format() function).
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your help.
    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    you're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •