Results 1 to 12 of 12
  1. #1
    lleeman is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2016
    Posts
    6

    Red face How can I calculate working days between two dates?

    I am a total MS Access novice so bear with me....I am trying to calculate the number of working days between two dates on a form.

    The VB code I currently have to do this is as follows:

    Private Sub All_Checks_Submitted_AfterUpdate()

    Me.Time_Taken1 = DateDiff("d", [Date_Received], [All_Checks_Submitted])

    End Sub



    ...but this calculates calendar days only. How can I change this to calculate working days only?

    Many thanks in advance

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    A simple search in google will give many examples. See for example: https://msdn.microsoft.com/en-us/lib.../ff191987.aspx
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    also look to the bottom of the thread for posts asking the same question

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    lleeman is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2016
    Posts
    6
    Thanks everyone for your helpful input. Excuse my ignorance but do I copy and paste this VBA function into the middle of the below procedure? Or where should it go?

    Private Sub All_Checks_Submitted_AfterUpdate()

    Me.Time_Taken1 = DateDiff("d", [Date_Received], [All_Checks_Submitted])

    End Sub

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    lleeman is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2016
    Posts
    6
    I don't care about holidays and weekend is Sat and Sunday so weekdays would be Mon-Friday

  8. #8
    lleeman is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2016
    Posts
    6
    Quote Originally Posted by orange View Post
    Do you care about holidays?
    Is your weekend Sat and Sunday or Other?
    No I don't care about holidays and weekend is Saturday & Sunday. Thanks

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi I noticed this is still not solved. Not sure if you are still looking but I use this function to work out Saturday and Sunday and bank holidays, you need to create a holiday table with all the holiday dates through out the year, I live in the UK so I have put all the Bank Holidays in that table. You will have Public holidays wherever you are. You then use the 'fnetworkdays(startdate,enddate) in a query and it works out the number of working days only for you, I did find that it does not include the last day so I added a +1 to the end of the function, like so.

    fnetworkdays(startdate,enddate)+1

    Hope this helps.

    Code:
    Option Compare Database
    Public Function fNetWorkdays(ByVal dtStartDate As Date, ByVal dtEndDate As Date, Optional blIncludeStartdate As Boolean = False) As Long
    'Returns the number of workdays between the two passed dates.  Saturdays and
    'Sundays are NOT considered workdays.  Plus there is an assumption that a
    'table exists that is named tblHolidays that identifies EACH holiday date
    'in a field named HolidayDate.  By default the function will NOT count the
    'first date in the range as a work date, if you pass a True value to
    'blIncludeStartdate, the function will count the start date as a work date
    'if it is not a Saturday,Sunday or Holiday.
    '''''''''''''''''''''''''''''''''''''''''''
    'Author: Brent Spaulding
    'Version: 8
    'Date: Jun 7 2011
    '''''''''''''''''''''''''''''''''''''''''''
    'Ver    Description
    '?-3    Intial releases to UA in various threads and the Code Archive
    '4      Made the function cabable of handling Start dates that are Greater
    '       than End dates
    '5      Fixed bug when the start date was a holiday and the SQL when end < start
    '6      Modified the structure a bit, logically equivalent, but I only test
    '       for dtStartDate <= dtEndDate once, instead of 3 times.
    '7      Formated date literals to corrected for possible errors with
    '       NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
    '8      Fixed but when start date is Weekend or Holiday and blIncludeStartdate was false.
    '..........................................
        
        Dim lngDays As Long
        Dim lngSaturdays As Long
        Dim lngSundays As Long
        Dim lngHolidays As Long
        Dim lngAdjustment As Long
        Dim blStartIsHoliday As Boolean
        Dim strSQL As String
        
        'Count the number of RAW days between the dates ...
        lngDays = Abs(DateDiff("d", dtStartDate, dtEndDate))
        
        'Count the number of Saturdays & Sundays between the two dates.  Note the use of "w" as
        'the date interval which will count the <day of first date in DateDiff()>.
        'So, to count the Saturdays, I adjust the start date of the datediff function
        'to the saturday BEFORE the dtStartDate of the passed range, thus the number
        'of Saturdays between the passed range is returned.  Investigated "ww"
        'for Sundays, but when the end is less than the start, problems arose.
        'This block also builds the SQL for extracting holidays.
        If dtStartDate <= dtEndDate Then
        
            lngSaturdays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, dtStartDate, dtStartDate - Weekday(dtStartDate, vbSunday)), dtEndDate))
        
            lngSundays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSunday, dtStartDate, dtStartDate - Weekday(dtStartDate, vbSunday) + 1), dtEndDate))
        
            strSQL = "SELECT HolidayDate FROM tblHolidays" & " WHERE HolidayDate" & " Between #" & Format(dtStartDate, "yyyy-mm-dd") & "#" & " And #" & Format(dtEndDate, "yyyy-mm-dd") & "#" & " And Weekday(HolidayDate, 1) Not In (1,7)" & " ORDER BY HolidayDate DESC"
        
        Else
        
            lngSaturdays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, dtStartDate, dtStartDate + (7 - Weekday(dtStartDate, vbSunday))), dtEndDate))
        
            lngSundays = Abs(DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSunday, dtStartDate, dtStartDate + (7 - Weekday(dtStartDate, vbSunday)) + 1), dtEndDate))
        
            strSQL = "SELECT HolidayDate FROM tblHolidays" & " WHERE HolidayDate" & " Between #" & Format(dtEndDate, "yyyy-mm-dd") & "#" & " And #" & Format(dtStartDate, "yyyy-mm-dd") & "#" & " And Weekday(HolidayDate, 1) Not In (1,7)" & " ORDER BY HolidayDate DESC"
        
        End If
        
        'Count the number of holidays AND determine if the start date is a holiday
        'the SQL is built in the IF..Then above.
        With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not .EOF Then
        
                'Get the number of holidays between the dates specified.
                'Need to populate a DAO recordset to ensure a good rcd count
                .MoveLast
        
                'Determine if the start date is a holiday.  Since the rst is
                'in descending order the last record SHOULD be no earlier than
                'the start date, so if the start date is equal to the LAST record
                'then, the start date is a holiday.... Unless we are in a "Negative"
                'situation, then the FIRST record must be checked.
                If dtStartDate > dtEndDate Then
                    .MoveFirst
                End If
        
                'Determine if the start is a holiday ... if it is, then DON'T include
                'it in the count of holidays since the first day is NOT included by
                'default in the total network days...
                blStartIsHoliday = (!HolidayDate = dtStartDate)
                If blStartIsHoliday Then
                    lngHolidays = .RecordCount - 1
                Else
                    lngHolidays = .RecordCount
                End If
        
            End If
            .Close
        End With
        
        'Make an adjustment based different situations ... basically if the start is
        'a weekend or holiday, the no need to include the start date, otherwise if
        'the start date is a workdate and the user specified to include it, then
        'adjust for that situation.
        '...Order of the Case statements is critical
        Select Case True
                
            Case Weekday(dtStartDate, vbSaturday) <= 2, blStartIsHoliday
                If dtStartDate = dtEndDate Then
                    lngAdjustment = 0
                Else
                    lngAdjustment = Not blIncludeStartdate
                End If
                            
            Case blIncludeStartdate
                lngAdjustment = 1
        
        End Select
        'Return the result
        fNetWorkdays = (lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment)
        If dtStartDate > dtEndDate Then
            fNetWorkdays = 0 - fNetWorkdays
        End If
        
    End Function
        
        
    Public Function fAddWorkdays(dtStartDate As Date, lngWorkDays As Long) As Date
    'Adds the passed number of workdays to a passed date.  This code uses
    'fNetWorkdays(), so the assumptions of tblHoliday apply for this function
    'as well. Also note that if a ZERO is entered as the lngWorkDays parameter
    'the function will return the start date, if its a work day, or the first
    'workday PRIOR to the dtStartdate.
    '''''''''''''''''''''''''''''''''''''''''''
    'Author: Brent Spaulding
    'Version: 7
    'Date: Aug 8 2008
    '''''''''''''''''''''''''''''''''''''''''''
    'Revision History:
    'Ver    Description
    '?-4    Intial releases to UA in various threads and the Code Archive
    '5      Made the function cabable of handling negative work days to add
    '6      Corrected for a DIV by Zero error when 0 was entered as lngWorkdays
    '       as well as some buggy stuff with negative workdays
    '7      Formated date literals to corrected for possible errors with
    '       NON-US Regional Settings (Thanks to UA user fazered for notification of issue!).
    '..........................................
        
        Dim dtEndDate As Date
        Dim lngDays As Long
        Dim lngSaturdays As Long
        Dim lngOffset As Long
        Dim lngSundays As Long
        
        'First ... GUESS at the End Date you need to cover the workdays you are adding.
        'I ASSUME that the number of days that are added will always toss you into a
        'week end, then I add the number of work weeks to it the get the number of
        'saturdays and sundays.
        lngSaturdays = 1 + Abs(lngWorkDays) \ 5
        lngSundays = lngSaturdays
        
        dtEndDate = DateAdd("d", Sgn(lngWorkDays) * (Abs(lngWorkDays) + lngSaturdays + lngSundays), dtStartDate)
        
        'Next, as much as I hate to do it, loop until the fNetWorkdays equals the number
        'of days requested.
        Do Until lngWorkDays = lngDays
        
            'Count the number of work days between the ESTIMATED end date
            'and the start date
            lngDays = fNetWorkdays(dtStartDate, dtEndDate, False)
        
            'Make an adjustment to the end date
            If lngDays <> lngWorkDays Then
                lngOffset = lngWorkDays - lngDays
                dtEndDate = dtEndDate + lngOffset
            End If
        
        Loop
        
        'Determine the offset direction to adjust for weekends and holidays
        'the offset trys to bring the end date CLOSER to the start date.
        If lngWorkDays < 0 Then lngOffset = 1 Else lngOffset = -1
        
        'Make sure the end day is NOT a holiday and NOT a Saturday/Sunday
        Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & Format(dtEndDate, "yyyy-mm-dd") & "#" & _
                                    " And Weekday([HolidayDate],1) Not In (1,7)") = 0 _
                 And Weekday(dtEndDate, vbMonday) < 6 '6th day of week if Mon is first day
            dtEndDate = dtEndDate + lngOffset
        Loop
        
        'Once we are out of the loop, the end date should be set to the correct date
        fAddWorkdays = dtEndDate
        
    End Function

  11. #11
    elandy90 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    1
    This solutions is a "Killer" but on the fNetWorkdays() function how i manage to add days with decimal numbers?

    Thanks
    elandy90

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @eland - Tony has not posted for 2 years - recommend you start a new thread as this one is 2 1/2 years old - by all means reference this post.

    you will need to expand on what you mean by using 'decimal numbers' - dates are stored as a double, not a decimal which is a different datatype

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. Replies: 5
    Last Post: 09-29-2015, 12:40 PM
  3. Replies: 2
    Last Post: 08-17-2015, 09:53 AM
  4. Calculate days between two dates by VBA
    By hhuuhn12 in forum Programming
    Replies: 16
    Last Post: 12-10-2013, 03:11 PM
  5. calculate no. of working days
    By JOSE LUIS in forum Access
    Replies: 1
    Last Post: 02-01-2010, 03:55 PM

Tags for this Thread

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