Calc Weekdays

[RuralGuy]

Understood. Should be pretty easy.

 

[RuralGuy]

OK, give this procedure a try.

Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
'-- Advance dteStart to Monday if needed.
   If Weekday(dteStart, vbMonday) > 5 Then
      PlusWorkdays = dteStart + (7 - Weekday(dteStart, vbMonday)) + 1
   Else
      PlusWorkdays = dteStart
   End If
   Do While intNumDays > 0
      PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
      '     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
      '-- If you have a Holiday Table use the next IF instead!
      If Weekday(PlusWorkdays, vbMonday) <= 5 And _
         IsNull(DLookup("[HoliDate]", "tblHolidays", _
                        "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
         '-- The above Format of PlusWorkdays works with US or UK dates!
         intNumDays = intNumDays - 1
      End If
   Loop
End Function

 

[scgoodman]

Saturday is showing right...i.e. shipped on 10/24/2009 due on 11/2/2009 with 5 day transit...however sunday is not calculating right.

I am showing if picked up 11/01/09 due on 11/10, should be 11/09.

Thanks for all your help!!

 

[RuralGuy]

Are you sure? It works correctly for me.

 

[RuralGuy]

Did you fall asleep?

 

[datAdrenaline]

.... hmmm .... I am no seeing where 5 days buisness days added to 10/24/2009 is 11/2/2009 ...

10/26/2002 = Day1
10/27/2002 = Day2
10/28/2002 = Day3
10/29/2002 = Day4
10/30/2002 = Day5

... Oh ... those are TRANSIT days, so delivery will be the next buisness day, 11/2/2009 ... right?


Here is what I use ... but before you say that's WAY too complex and long, it is loaded with comments. Drop both in a module that does not share a name with either function. fAddWorkdays() is dependant upon fNetWorkdays(), so you need then both. You will probably find this code to be a little faster (no offense intented to RG) since I do not call a DLookup() for each day, nor do I loop each day .... but the code is longer.

Example (note: 5 transit days, then the next buisness day is delivery/due):

? fAddWorkdays(#10/24/2009#,5 + 1)
11/2/2009

? fAddWorkdays(#11/01/2009#,5 + 1)
11/9/2009

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: 7
'Date: Aug 8 2008
'''''''''''''''''''''''''''''''''''''''''''
'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!).
'..........................................
 
    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
    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
            lngAdjustment = 0
 
        Case blIncludeStartdate
            lngAdjustment = 1
 
    End Select
 
    'Return the result
    If dtStartDate > dtEndDate Then
        fNetWorkdays = 0 - (lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment)
    Else
        fNetWorkdays = (lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment)
    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

 

[dsfcom]

Just a quick question concerning this thread...what type of field is in 'tblHolidays'? Is it a date containing a full date or a text containing only the month/day?

Also, i'm trying to use thte same function and calling it from a different function with:

fNetWorkdays(strStartDt, strEndDt, True)

I am getting a 'Compile Error: Expected: ='. My strStartDt and strEndDt are driven by form entries (i.e. Forms![frmPersonnel_Tasks_Task_Ttl_Time_Date_Range].[txtStartDT]). I can calculate date differences but need to leave out weekends and holidays but am having trouble calling the function.

 

[gemma-the-husky]

you can do all this with this sort of logic


get the startdate
set workingdays to 0
set a workingdate equal to the startdate

LOOP START
add 1 to the workingdate

test this date to see if you want to use this
(you can directly use constructs like - if weekday(workingdate) = vbsunday
you will need to do special tests for easter/christmas etc etc)

if you want to count this as a workingday, just add 1 to the workingdays count

if workingdays count is now equal to your target then finish.
otherwise you need to advance the date further, so GO BACK TO LOOP START

when you finish, the workingdate is now set as you required


its a lot easier to code this than it sounds (although the later postings are getting quite complicated), and you have most if not all of the functionality already

 

[datAdrenaline]

>> Just a quick question concerning this thread...what type of field is in 'tblHolidays'? Is it a date containing a full date or a text containing only the month/day? <<

tblHolidays is a table with the following stucture:

tblHolidays
----------
HolidayDate (Date/Time, Unique Index)
<any other fields are optional>

.....

>> I am getting a 'Compile Error: Expected: =' <<

Where (what line of code) does Access take you when you get the compile error? Also, how do you account for Nulls (ie: blank text boxes). When do you get this compile error?

 

[datAdrenaline]

Hello Dave ...

While the logic you describe is easy to code for, it is also a bit slower -- especially if the date range is long. The code I have posted, while long (it is filled with comments ), has successfully been in use by many, and has been tested as faster than the looping you are describing, thus the reason for the encouragement to go with the code I posted. There are, however, many ways to get to the same result ...