[X]My Assistant

[datAdrenaline]

The test for Weekday(<date>,1) In (1,7) added about 20 - 30 milliseconds to the times poste. Its been a couple of days since my testing ... so when I have the opportunity, I will see if its much different than that. (Note: in my holiday table, the field HolidayDate is indexed)

[Brandi]

Brent,
I am trying to revise your code below to work in a situation where I want to calculate the End Date instead of the number of working days. For example if my start date is 1/15/07, I want to know what the End Date will be 15 workdays from my Start Date. (minus holidays and Sat and Sun). I started out with the following and then got stuck. Is it possible to revise this to return the End Date? The number of workdays from the Start Date will be variable so I will pass that to the function.
ublic Function CalcEndDate(dtStartDate As Date, lngDays As Long) As Date
Your original code:
Public Function NetWorkdays(dtStartDate As Date, dtEndDate As Date) 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.
'''''''''''''''''''''''''
'Author: Brent Spaulding
I'Version: 2
'Date: 1/11/2007
''''''''''''''''''''

Dim lngDays As Long
Dim lngSaturdays As Long
Dim lngSundays As Long
Dim lngHolidays As Long
Dim lngAdjustment As Long

'Count the number of RAW days between the dates
lngDays = DateDiff("d", dtStartDate, dtEndDate)

'Count the number of Sundays between the two days. Note the use of "ww" as
'the date interval which forces the count of sundays
lngSundays = DateDiff("ww", dtStartDate, dtEndDate, vbSunday)

'Count the number of Saturdays 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.
lngSaturdays = DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
dtStartDate, _
dtStartDate - Weekday(dtStartDate, vbSunday)), _
dtEndDate)

'Count the number of holidays
lngHolidays = Nz(DCount("*", "tblHolidays", _
"HolidayDate Between #" & dtStartDate & _
"# And #" & dtEndDate & "# And " & _
" Weekday(HolidayDate, 1) Not In (1,7)"), 0)

'Make an adjustment based on whether or not the start date is a Saturday
If Weekday(dtStartDate, vbSunday) = vbSunday Or Weekday(dtStartDate, vbSunday) = vbSaturday Then
lngAdjustment = 0
Else
lngAdjustment = 1
End If

'Return the result
NetWorkdays = lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment

End Function

[datAdrenaline]

Here is an updated fNetWorkdays {Optionally INCLUDES the first day of the date range} as well as the function fAddWorkDays() ... I plan to put both in the code archive ... I will post the link when I do.
!--c1-->

CODE

Public Function fNetWorkdays(dtStartDate As Date, 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
I'Version: 3
'Date: 1/13/2007
'''''''''''''''''''''''''''''''''''''''''''
    
    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 = DateDiff("d", dtStartDate, dtEndDate)
    
    'Count the number of Sundays between the two days. Note the use of "ww" as
    'the date interval which forces the count of sundays
    lngSundays = DateDiff("ww", dtStartDate, dtEndDate, vbSunday)
    
    'Count the number of Saturdays 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.
    lngSaturdays = DateDiff("w", IIf(Weekday(dtStartDate, vbSunday) = vbSaturday, _
                                dtStartDate, _
                                dtStartDate - Weekday(dtStartDate, vbSunday)), _
                             dtEndDate)
    
    'Count the number of holidays AND determine if the start date is a holiday
    strSQL = "SELECT HolidayDate FROM tblHolidays" & _
             " WHERE HolidayDate Between #" & dtStartDate & _
                               "# And #" & dtEndDate & "# And" & _
                               " Weekday(HolidayDate, 1) Not In (1,7)" & _
             " ORDER BY HolidayDate DESC"
    
    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
            lngHolidays = .RecordCount
            
            '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 FIRST record
            'then, the start date is a holiday.
            If !HolidayDate = dtStartDate Then
                blStartIsHoliday = True
            End If
            
        End If
    End With
    
    'Make an adjustment based on whether or not the start date is to be
    'included in the calc
    If (Weekday(dtStartDate, vbSunday) = vbSunday) Or _
       (Weekday(dtStartDate, vbSunday) = vbSaturday) Or _
       blStartIsHoliday = True Then
        lngAdjustment = 0
    Else
        If blIncludeStartdate = True Then
            lngAdjustment = 1
        End If
    End If
    
    'Return the result
    fNetWorkdays = lngDays - lngSundays - lngSaturdays - lngHolidays + lngAdjustment
    
End Function
    
    
Public Function fAddWorkDays(dtStartDate As Date, _
                             lngWorkDays As Variant, _
                             Optional blIncludeStartdate As Boolean = False) _
                             As Date
    
    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 + lngWorkDays \ 5  'this is Integer Division
    lngSundays = lngSaturdays
    
    dtEndDate = DateAdd("d", 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, blIncludeStartdate)
        
        'Make an adjustment to the end date
        If lngDays <> lngWorkDays Then
            lngOffset = lngWorkDays - lngDays
            dtEndDate = dtEndDate + lngOffset
        End If
            
    Loop
    
    'Make sure the end day is NOT a Saturday/Sunday
    Do Until Weekday(dtEndDate, vbMonday) < 6 '6th day of the week with Mon as first day
        dtEndDate = dtEndDate - 1
    Loop
    
    'Make sure the end day is NOT a holiday
    Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & dtEndDate & "#" & _
                                    " And Weekday([HolidayDate],1) Not In (1,7)") = 0
        dtEndDate = dtEndDate - 1
    Loop
    
    'Once we are out of the loop, the end date should be set to the correct date
    fAddWorkDays = dtEndDate
    
End Function

Please let me know if you have any issues/questions/etc...

[datAdrenaline]

Correction to comment block ....
ncorrect:
'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 FIRST record
'then, the start date is a holiday.
CORRECT:
'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.
.... Sorry for the confusion ....

[Brandi]

I just got a chance to try the new function today.
Both functions work great!
Thank you again.
Brandi

[datAdrenaline]

[datAdrenaline]

I just recently updated the fNetworkdays() and fAddworkdays() code archive post. Please update your code with the latest version at your discretion. Click here for the code archive post

[Brandi]

I have used a combination of all your suggestions above. My code includes Sat and Sundays as counting for a day except at the end when the end date falls on a Sat or Sunday in which case the end date is moved to Monday.
It is now many months later and my customer is changing their rule.
If the end date turns out to fall on a Sat or Sunday then the current code moves the end date to a Monday.
Now, however, they are telling me if the end date falls on a Sat or Sunday, then they want the previous Friday to be the end date.
I was not sure how to modify the code below because if the weekday = 6, then I would want to subtract 1 from the end date but if the weekday = 7 then I would want to subtract 2 from the enddate. I can verbalize this but now sure how to put that into the code.
'Make sure the end day is NOT a Saturday/Sunday
Do Until Weekday(dtEndDate, vbMonday) < 6 '6th day of the week with Mon as first day
dtEndDate = dtEndDate + 1
Loop

'Make sure the end day is NOT a holiday
Do Until DCount("*", "tblHolidays", "[HolidayDate]=#" & dtEndDate & "#" & _
" And Weekday([HolidayDate],1) Not In (1,7)") = 0
dtEndDate = dtEndDate + 1
Loop

'Once we are out of the loop, the end date should be set to the correct date
AddWorkDaysWithSatSun = dtEndDate
Thank you for your help.
Brandi

[elandy90]

Hello Brandy,

I have a question for you. I'm trying your function work days and for the function fAddWorkdays() how can i add the lngWorkDays as a decimal data?

Can you help me?

Note: Thank you for your knowledge sharing

Best Regards,

elandy