Working Days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello.
I have a table with several records. One of the fields have a date.
What i'd like to do is to count the working days between that date and the
system date.
Is it possible ?

Thanks.

Luis
 
Here is a routine that counts working days between to dates. You will need a
holiday table to exclude holidays. In the is routine, the table is named
Holidays and the holiday date in [Holdate]. It will exclude all Saturdays,
Sundays, and dates in the holiday table.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculated the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

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("[Holdate]", "Holidays", _
"[Holdate] = #" & 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
 
Back
Top