CalcWorkDays error

  • Thread starter Thread starter Sue Compelling
  • Start date Start date
S

Sue Compelling

Hi (Access2007)

I'm trying to get Klaatu's CalcWorkDays function to work though something's
not quite right in the results. Would appreciate any help that you can
provide. The first row's results are right though the following don't seem
to calculate.

My query results look like this:

In Out DateDiffDays
KlaatuDays
(excl Sat/Sun and last day)
(also excl Pub Hols)
1/02/2010 20/02/2010 14 11
9/02/2010 25/02/2010 12 12
10/02/2010 15/04/2010 46 46

My Query expressions look like this:

KlaatuDays: calcworkdays([in],[out])
DateDiffDays:
DateDiff("d",[In],[out]-(DateDiff("ww",[In],[out],7)+DateDiff("ww",[in],[out],1)))

My "HolDate" (in my Table "Holidays") are this:

HolDate
6/02/2010 (which is a Sat - I was trying to test results though will
take this out)
18/02/2010
19/02/2010

My function looks like this:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Have not added one as I wantto exclude the last date
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1))
'Subtract the Holidays
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
 
Thanks Ken - worked a treat and really appreciated the demo as well ....
--
Sue Compelling


KenSheridan via AccessMonster.com said:
Sue:

I use the following function, which uses a table Holidays with a column
HolDate of date/time data type. The reason for the name of the function BTW
is that I also have one which takes a Country as another argument and allows
for different sets of public holidays in different countries:

Public Function WorkDaysDiff_SingleCountry(varFirstDate As Variant, _
varLastDate As Variant, _
Optional blnExcludePubHols As Boolean = False) As
Variant

Dim lngDaysDiff As Long, lngWeekendDays As Long
Dim intPubHols As Integer

If IsNull(varLastDate) Or IsNull(varFirstDate) Then
Exit Function
End If


' if first date is Sat or Sun start on following Monday
Select Case WeekDay(varFirstDate, vbSunday)
Case vbSaturday
varFirstDate = varFirstDate + 2
Case vbSunday
varFirstDate = varFirstDate + 1
End Select

' if last date is Sat or Sun finish on following Monday
Select Case WeekDay(varLastDate, vbSunday)
Case vbSaturday
varLastDate = varLastDate + 2
Case vbSunday
varLastDate = varLastDate + 1
End Select

' get total date difference in days
lngDaysDiff = DateDiff("d", varFirstDate, varLastDate)

' get date difference in weeks and multiply by 2
' to get number of weekend days
lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2

' subtract number of weekend days from total date difference
' to return number of working days
WorkDaysDiff_SingleCountry = lngDaysDiff - lngWeekendDays

' exclude public holidays if required
If blnExcludePubHols Then
intPubHols = DCount("*", "Holidays", "HolDate Between #" _
& Format(varFirstDate, "mm/dd/yyyy") & "# And #" & _
Format(varLastDate - 1, "mm/dd/yyyy") & "#")

WorkDaysDiff_SingleCountry = WorkDaysDiff_SingleCountry - intPubHols
End If

End Function

The optional blnExcludePubHols argument allows for public holidays to be
taken into account or not. By default its False.

With your holiday dates, excluding the Saturday date of 6/02/2010, it returns
values as follows:

? WorkDaysDiff_SingleCountry(#2010-02-01#,#2010-02-20#,True)
13
? WorkDaysDiff_SingleCountry(#2010-02-09#,#2010-02-25#,True)
10
? WorkDaysDiff_SingleCountry(#2010-02-10#,#2010-04-15#,True)
44

Is that what you expect?

I'm mailing you the file which incorporates the functions, and a form for
trying it out, though the form uses the function which allows for different
countries (you can add New Zealand), so select England at present as its set
up with the constituent countries of the UK plus the Republic of Ireland.

Ken Sheridan
Stafford, England

Sue said:
Hi (Access2007)

I'm trying to get Klaatu's CalcWorkDays function to work though something's
not quite right in the results. Would appreciate any help that you can
provide. The first row's results are right though the following don't seem
to calculate.

My query results look like this:

In Out DateDiffDays
KlaatuDays
(excl Sat/Sun and last day)
(also excl Pub Hols)
1/02/2010 20/02/2010 14 11
9/02/2010 25/02/2010 12 12
10/02/2010 15/04/2010 46 46

My Query expressions look like this:

KlaatuDays: calcworkdays([in],[out])
DateDiffDays:
DateDiff("d",[In],[out]-(DateDiff("ww",[In],[out],7)+DateDiff("ww",[in],[out],1)))

My "HolDate" (in my Table "Holidays") are this:

HolDate
6/02/2010 (which is a Sat - I was trying to test results though will
take this out)
18/02/2010
19/02/2010

My function looks like this:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Have not added one as I wantto exclude the last date
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1))
'Subtract the Holidays
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
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top