Counting business day from one date

  • Thread starter Thread starter dzicopula
  • Start date Start date
D

dzicopula

I am trying to create a report and want to count 7 business days from
an entry date. The entry date occurs in the past (not today's date).
All of the code I find is for counting the days between two days.
Please help!
 
Try this function:


Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date

PlusWorkdays = dteStart
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("[Holiday]", "tblHolidays", _
"[HolDate] = " & 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
 
Here is the one I use:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

One thing to review. As written, it includes both the beginning and ending
days in the count. For example, if you enter Today (#8/30/2007#) and
#9/6/2007#, it will return 5. It will exclude Labor Day (U.S. #9/1/2007#)
and #9/2/2007# Saturday and #9/3/2007# Sunday. If you don't want to include
the beginning day in the count, just modify this line:

DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

To
DateDiff("ww", dtmStart, dtmEnd, 1))

And given the above, it will return 4.
 
Back
Top