W
wpshop
I am using the calcworkdays code in a database I am using to calculate the
turn around time for certain processes. On occassion it is possible for an
item to open on a weekend/holiday and close that same day. Right now I am
getting a result of -1 but I need it to be "0". Is there something I can add
to the following code to accomplish this task?
Option Compare Database
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis
intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[HolidayDate]", "tblHolidays", _
"[HolidayDate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd 'All days have
been compared
CalcWorkDays = intTotalDays 'Return the value
End Function
turn around time for certain processes. On occassion it is possible for an
item to open on a weekend/holiday and close that same day. Right now I am
getting a result of -1 but I need it to be "0". Is there something I can add
to the following code to accomplish this task?
Option Compare Database
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis
intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[HolidayDate]", "tblHolidays", _
"[HolidayDate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd 'All days have
been compared
CalcWorkDays = intTotalDays 'Return the value
End Function