Results 1 to 6 of 6
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Calculate Number of Business Days Between Two Dates


    Anyone have a simple VBA solution to calculate the number of business days between two dates?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    From the similar thread section below:

    https://www.accessforums.net/showthread.php?t=58953
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The following is a simplified version (9 lines of code) I was able to figure out for calculating the total number of business days between two dates.

    Code:
    Dim BusinessDays As Integer
    Dim TotalDays As Integer
    Dim Sunday As Integer
    Dim Saturday As Integer
    TotalDays = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
    Sunday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 1)
    Saturday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 7)
    BusinessDays = TotalDays - Sunday - Saturday
    Me![txtBusinessDaysCalculated] = BusinessDays
    The following is an image of my calculator followed by the corresponding code:
    Click image for larger version. 

Name:	Capture.JPG 
Views:	49 
Size:	35.1 KB 
ID:	39468

    Code:
    Private Sub btnCalculate_Click()
    On Error GoTo btnCalculate_Click_Err
    'Calculate Number of Days, Hours, and Minutes
        Me![txtDateDaysCalculated] = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
        Me![txtDateHoursCalculated] = DateDiff("h", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
        Me![txtDateWeeksCalculated] = [txtDateDaysCalculated] / 7
        Me![txtTimeHoursCalculated] = Int(DateDiff("n", [txtBeginTimeRangeFilterCalculator], [txtEndTimeRangeFilterCalculator]) / 60)
        Me![txtTimeMinutesCalculated] = Format(DateDiff("n", [txtBeginTimeRangeFilterCalculator], [txtEndTimeRangeFilterCalculator]) Mod 60, "00")
        Me![txtHourFraction] = [txtMinutesToCalculate] / 60
    'Calculate Number of Business Days
        Dim BusinessDays As Integer
        Dim TotalDays As Integer
        Dim Sunday As Integer
        Dim Saturday As Integer
        TotalDays = DateDiff("d", [txtBeginDateRangeFilterCalculator] - 1, [txtEndDateRangeFilterCalculator], vbSunday)
        Sunday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 1)
        Saturday = DateDiff("ww", [txtBeginDateRangeFilterCalculator], [txtEndDateRangeFilterCalculator], 7)
        BusinessDays = TotalDays - Sunday - Saturday
        Me![txtDateBusinessDaysCalculated] = BusinessDays
    btnCalculate_Click_Exit:
        Exit Sub
    btnCalculate_Click_Err:
        Beep
        MsgBox Err.Description, vbOKOnly, ""
        Resume btnCalculate_Click_Exit
    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's fine if you don't need holidays excluded. Many situations do.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The issue with tracking holidays is not everyone in the business world has holidays off. Moreover, we cannot feasibly track the number of "business days" before or after the holiday that people take off either. So I am not concerned with tracking holidays. It is easier for the person who is using the calculator to subtract the number of business days the person is not going to be in the office for a given holiday than it is to program that.

    The other problem is, for example, Martin Luther King Day. The holiday is/was not always observed on January 15. I remember different entities years ago observing it on January 17.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	38 
Size:	81.1 KB 
ID:	39501

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think in normal usage it isn't intended to account for anybody's holidays other than the company running the app. That's why there's normally a holidays table (as in the code referenced above). I just wanted to point out that your method didn't account for them in case someone searching for a solution later comes upon this thread and their situation does need to account for them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-16-2019, 08:36 AM
  2. Calculate Business Hours over X amount of business days.
    By gutarkomp in forum Code Repository
    Replies: 5
    Last Post: 05-16-2017, 06:23 PM
  3. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  4. Calculate days between two dates by VBA
    By hhuuhn12 in forum Programming
    Replies: 16
    Last Post: 12-10-2013, 03:11 PM
  5. Replies: 3
    Last Post: 04-03-2013, 05:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums