Calcworkdays Revision?

[Guest]

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

 

[Guest]

You could change the line that reads:

Do Until dtmToday > dtmEnd

to

Do Until dtmToday >= dtmEnd

'This method woud skip the weekend and holiday tests for the ending day of
the process, no matter what day that is. So if, for example, a process
completes on a weekend or holiday, then this would count that day

HTH
Dale

 

[Guest]

That worked perfectly. I appreciate your assistance!

You could change the line that reads:

Do Until dtmToday > dtmEnd

to

Do Until dtmToday >= dtmEnd

'This method woud skip the weekend and holiday tests for the ending day of
the process, no matter what day that is. So if, for example, a process
completes on a weekend or holiday, then this would count that day

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

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