Hi,

I have found the following post for calculating the diff between 2
dates excluding holidays and weekends:

http://groups.google.co.uk/group/mic...fa782204?hl=en

Now I can get it to work for just weekends, but am struggling with the
3rd argument for holidays.

I have a table called 'Holidays' which contains a list of the dates so
thought the code would be:

SELECT Avg(dhCountWorkdaysA([DateReceived],[FirstContactAttempt],
[Holidays])) AS RectToFirstContact
FROM Journey_MI;

However, it is bringing up the input box for holidays so isn't
recognising it correctly.

Does anyone have any ideas?

Thanks.

On Aug 12, 4:55*am, macroapa <macro...@hotmail.co.uk> wrote:
> Hi,
>
> I have found the following post for calculating the diff between 2
> dates excluding holidays and weekends:
>
> http://groups.google.co.uk/group/mic...s/msg/1456554b...
>
> Now I can get it to work for just weekends, but am struggling with the
> 3rd argument for holidays.
>
> I have a table called 'Holidays' which contains a list of the dates so
> thought the code would be:
>
> SELECT Avg(dhCountWorkdaysA([DateReceived],[FirstContactAttempt],
> [Holidays])) AS RectToFirstContact
> FROM Journey_MI;
>
> However, it is bringing up the input box for holidays so isn't
> recognising it correctly.
>
> Does anyone have any ideas?
>
> Thanks.

this works
http://www.datastrat.com/Code/GetBusinessDay.txt

>
> this workshttp://www.datastrat.com/Code/GetBusinessDay.txt- Hide quoted text -
>
> - Show quoted text -

Thanks, but that has the format:

GetBusinessDay(datStart As Date, intDayAdd As Integer)

so thats adding business days onto the start date as opposed to
calc'ing the date different between 2 dates????

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, so in your query, to
exclude weekends and holidays from the count you'd call it like so:

SELECT
AVG(WorkDaysDiff_SingleCountry([DateReceived],[FirstContactAttempt],TRUE))
AS RectToFirstContact
FROM Journey_MI;

Ken Sheridan
Stafford, England

"macroapa" wrote:

> Hi,
>
> I have found the following post for calculating the diff between 2
> dates excluding holidays and weekends:
>
> http://groups.google.co.uk/group/mic...fa782204?hl=en
>
> Now I can get it to work for just weekends, but am struggling with the
> 3rd argument for holidays.
>
> I have a table called 'Holidays' which contains a list of the dates so
> thought the code would be:
>
> SELECT Avg(dhCountWorkdaysA([DateReceived],[FirstContactAttempt],
> [Holidays])) AS RectToFirstContact
> FROM Journey_MI;
>
> However, it is bringing up the input box for holidays so isn't
> recognising it correctly.
>
> Does anyone have any ideas?
>
> Thanks.
>

On Tue, 12 Aug 2008 03:12:16 -0700 (PDT), macroapa <macroapa@hotmail.co.uk> wrote:

>
>>
>> this workshttp://www.datastrat.com/Code/GetBusinessDay.txt- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks, but that has the format:
>
>GetBusinessDay(datStart As Date, intDayAdd As Integer)
>
>so thats adding business days onto the start date as opposed to
>calc'ing the date different between 2 dates????

Although seldom mentioned, you can also use a auxillary calendar table to do these kinds of
calculations. Find one here:
http://www.psci.net/gramelsp/temp/Sa...s%202003. htm

Take a look at my September, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"macroapa" <macroapa@hotmail.co.uk> wrote in message
news:09e14ff8-39e5-44bb-b0f3-4a4d07c99af5@w7g2000hsa.googlegroups.com...
> Hi,
>
> I have found the following post for calculating the diff between 2
> dates excluding holidays and weekends:
>
> http://groups.google.co.uk/group/mic...fa782204?hl=en
>
> Now I can get it to work for just weekends, but am struggling with the
> 3rd argument for holidays.
>
> I have a table called 'Holidays' which contains a list of the dates so
> thought the code would be:
>
> SELECT Avg(dhCountWorkdaysA([DateReceived],[FirstContactAttempt],
> [Holidays])) AS RectToFirstContact
> FROM Journey_MI;
>
> However, it is bringing up the input box for holidays so isn't
> recognising it correctly.
>
> Does anyone have any ideas?
>
> Thanks.

I'll second that. For one thing it has the advantage of portability.

Ken Sheridan
Stafford, England

"Michael Gramelspacher" wrote:

> On Tue, 12 Aug 2008 03:12:16 -0700 (PDT), macroapa <macroapa@hotmail.co.uk> wrote:
>
> >
> >>
> >> this workshttp://www.datastrat.com/Code/GetBusinessDay.txt- Hide quoted text -
> >>
> >> - Show quoted text -
> >
> >Thanks, but that has the format:
> >
> >GetBusinessDay(datStart As Date, intDayAdd As Integer)
> >
> >so thats adding business days onto the start date as opposed to
> >calc'ing the date different between 2 dates????
>
> Although seldom mentioned, you can also use a auxillary calendar table to do these kinds of
> calculations. Find one here:
> http://www.psci.net/gramelsp/temp/Sa...s%202003. htm
>