Determine Date X Work Days in Past or Future 1

[N2Life]

I needed this function to determine a date that was a certain number of work days in the past. It also handles future dates. This may save a fellow Access user some time.

Public Function WorkDate(StartingDate As Date, DaysDiff As Integer) As Date
' This function is concerned only with work days (not Saturday or Sunday or a holiday).
' It assumes there is a table of holidays (tblHolidays) with a field HDate.
' It determines the date that occurs a given number of work days prior to or after a given date.
' Example: WorkDate(#11/29/2006", -17) returns the date that occurs 17 work days prior to Nov. 29, 2006.
' Result would be 11/2/2006, provided we have entered 11/23/2006 and 11/24/2006 into tblHolidays.

Dim TotalWorkDays As Integer ' to hold the count of work days found
Dim CheckDate As Date ' a copy of StartingDate to add to or subtract from
TotalWorkDays = 0 ' set to zero before starting the hunt
CheckDate = StartingDate ' make a copy of StartingDate
' We will have finished when TotalWorkDays is the same as DaysDiff (disregarding any negative sign for DaysDiff).
Do Until TotalWorkDays = Abs(DaysDiff)
' If DaysDiff is negative, we are looking for a date in the past; otherwise, in the future.
If DaysDiff < 0 Then
CheckDate = CheckDate - 1 ' Decrement to get previous date.
Else
CheckDate = CheckDate + 1 ' Increment to get next date.
End If
' If the resulting CheckDate is neither a Sunday, nor a Saturday, nor present in
' the table of holidays, then this CheckDate qualifies as a work day...
If Not (Weekday(CheckDate) = vbSunday Or _
Weekday(CheckDate) = vbSaturday Or _
DCount("HDate", "tblHolidays", "HDate=#" & CheckDate & "#") > 0) Then
' ...we can add it to our count of TotalWorkDays.
TotalWorkDays = TotalWorkDays + 1
End If
Loop
WorkDate = CheckDate
End Function

 

[hneal98]

Great function. Just one little thing, in your example, you show "WorkDate(#11/29/2006", -17)". Is it supposed to be like that or should it be "WorkDate(#11/29/2006#, -17)"?

 

[N2Life]

hneal98,

Thanks so much. I proof-read and proof-read - or at least I thought I did. Corrected:

Public Function WorkDate(StartingDate As Date, DaysDiff As Integer) As Date
' This function is concerned only with work days (not Saturday or Sunday or a holiday).
' It assumes there is a table of holidays (tblHolidays) with a field HDate.
' It determines the date that occurs a given number of work days prior to or after a given date.
' Example: WorkDate(#11/29/2006#, -17) returns the date that occurs 17 work days prior to Nov. 29, 2006.
' Result would be 11/2/2006, provided we have entered 11/23/2006 and 11/24/2006 into tblHolidays.

Dim TotalWorkDays As Integer ' to hold the count of work days found
Dim CheckDate As Date ' a copy of StartingDate to add to or subtract from
TotalWorkDays = 0 ' set to zero before starting the hunt
CheckDate = StartingDate ' make a copy of StartingDate
' We will have finished when TotalWorkDays is the same as DaysDiff (disregarding any negative sign for DaysDiff).
Do Until TotalWorkDays = Abs(DaysDiff)
' If DaysDiff is negative, we are looking for a date in the past; otherwise, in the future.
If DaysDiff < 0 Then
CheckDate = CheckDate - 1 ' Decrement to get previous date.
Else
CheckDate = CheckDate + 1 ' Increment to get next date.
End If
' If the resulting CheckDate is neither a Sunday, nor a Saturday, nor present in
' the table of holidays, then this CheckDate qualifies as a work day...
If Not (Weekday(CheckDate) = vbSunday Or _
Weekday(CheckDate) = vbSaturday Or _
DCount("HDate", "tblHolidays", "HDate=#" & CheckDate & "#") > 0) Then
' ...we can add it to our count of TotalWorkDays.
TotalWorkDays = TotalWorkDays + 1
End If
Loop
WorkDate = CheckDate
End Function

 

[hneal98]

No problem. That is why we are all here.

 

[MichaelRed]

I always appreciate the effort to solve problems on one's own. On the other hand there are several faqs re the topic of retrieving such dates and other related issues, so you sould have saved yourself the effort with a search ...




MichaelRed