Working days

C

corfiotis

How i can mark some days of the year as not working (saturdays,sundays,
christmas day , New year , etc.) and then betweeen an [starting Date] and
[ending day] to calculate the number of working days.

Thank you in advance

Achilles
 
K

Klatuu

Here is a function I use for that purpose. You will need a table to store
holidays. The function knows weekend days, so you don't have to worry about
them.
My table is named "Holidays" (clever, huh?). It has two fields, Holdate
which is a date field, and a description field.

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
 
C

corfiotis

Thank you very much. Your idea is more easily understand by me the the one
OFFER gives me.

Please if possible explain where i ll use the function you gave me?In a
field counting the Diffdate?

Ο χÏήστης "Klatuu" έγγÏαψε:
Here is a function I use for that purpose. You will need a table to store
holidays. The function knows weekend days, so you don't have to worry about
them.
My table is named "Holidays" (clever, huh?). It has two fields, Holdate
which is a date field, and a description field.

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


corfiotis said:
How i can mark some days of the year as not working (saturdays,sundays,
christmas day , New year , etc.) and then betweeen an [starting Date] and
[ending day] to calculate the number of working days.

Thank you in advance

Achilles
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Actually, it would probably be better not to include the DLookup to the
Holidays table inside your loop: it's a little inefficient.

Instead, use

DCount("*", "Holiday", "[Holdate] Between " & _
Format(dtmStart, "\#mm\/dd\/yyyy\#") & " And " & _
Format(dtmEnd, "\#mm\/dd\/yyyy\#"))

to determine the total number of holiday days between the two dates, and
subtract that from the total you get without holidays.

In actual fact, though, you don't really need the loop at all. Take a look
at my September, 2004 "Access Answers" column in Pinnacle Publication's
"Smart Access". You can download the column and accompanying database for
free at http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Klatuu said:
Here is a function I use for that purpose. You will need a table to store
holidays. The function knows weekend days, so you don't have to worry
about
them.
My table is named "Holidays" (clever, huh?). It has two fields, Holdate
which is a date field, and a description field.

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


corfiotis said:
How i can mark some days of the year as not working (saturdays,sundays,
christmas day , New year , etc.) and then betweeen an [starting Date]
and
[ending day] to calculate the number of working days.

Thank you in advance

Achilles
Click to expand...
Click to expand...
 
Top