How do I calculate days?

[hanski]

Hi.

Is there any function in Access 2000, which can calculate dates so that
it ignores saturdays and sundays and also any other days, which I could
input myself?

For instance I would like to know how many days are there between 1. of
April to 30. of April, but I do not want to involve weekends and 11. of
April?

Hannu

 

[Rick B]

This is a very frequent question. Do a search for "working days" or
"business days".

 

[Douglas J Steele]

Take a look at http://www.mvps.org/access/datetime/date0012.htm at "The
Access Web", or my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access" (and the June, 2005 follow-up). You can
download the columns (and sample databases) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Guest]

Here is a function that does exactly what you want. You will need a Holiday
table to hold dates you don't want included in the calculation. In this
function, the table name is Holidays and the date field is HolDate. You can
change them to suit your table and field names.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'Calculates 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

 

[Ron2006]

An slightly different alternative

1) Compute the number of working days between two dates:

workdays
=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
],7)+DateDiff("ww",[StartDate],[EndDate],1))

You now have the total number of weekdays between two dates.

2) Create a table of holidays. The same as you have in your excell
spreadsheet

now

write a query that selects all records in that table between the
specified dates and use

holidaycnt= dCount ("[fieldnameinholidaytable]",
"theabovecreatedquery")

Subtract 2 from1 and you have the actual workdays.

 

[Guest]

Very clever formula; however, in calculationg working days, it is one short.
for example, if you use 5/1/2006 through 5/10/2006, the total number of
working days is 8. The formula returns the difference, but does not include
them all. So, to include all working days, you just need to add 1 to your
formula:

=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate], _
[EndDate],7)+DateDiff("ww",[StartDate],[EndDate],1)) + 1

Just for grins, I am going to write a version of my routine using the
formula above and a Dcount on the holidays table to see which execute faster.
I suspect yours will.

 

[Guest]

Okay, here is the new and improved function. With only a few days, it is
hard to see any difference. Over an 18 month test, the new version is much
faster.

Thanks, Ron

'---------------------------------------------------------------------------------------
' 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

CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
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

 

[Ron2006]

Yes, thank you for the reminder about the extra day. I remembered it
the last time I posted that solution, but forgot about it this time.