Avg # of days minus weekends

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Trying to create a query which will perform the following
function.

Take a date field [Date received] and another date field
[Date resolved]. Generate an average of how long it takes
to resolve. Here's the expression I'm using.

AverageDate: Avg([Date resolved]-[Date reported])

This is working fine, but now I need to add in one more
feature. I want to know only the average number of
weekdays (Monday through Friday) that it takes. Is this
possible?
 
Trying to create a query which will perform the following
function.

Take a date field [Date received] and another date field
[Date resolved]. Generate an average of how long it takes
to resolve. Here's the expression I'm using.

AverageDate: Avg([Date resolved]-[Date reported])

This is working fine, but now I need to add in one more
feature. I want to know only the average number of
weekdays (Monday through Friday) that it takes. Is this
possible?
Click to expand...

Hi Jeff,

Save the following function in a Module
(if this will be a new module, do not name
the new module the same as function name)

Public Function DateDiffW(BegDate, EndDate)
'adapted from http://support.microsoft.com/?kbid=288194
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)

'tacked on " +1" at end of following formula
'to count "up to and including" EndDate;
'Also, if same working day, will return 1,
'but, if same non-workingday, will return 0;

DateDiffW = NumWeeks * 5 + _
Weekday(EndDate) - Weekday(BegDate) + 1
End If
End Function

Then you might use in your query:

AverageWorkDays: Avg(DateDiffW([Date reported],[Date resolved]))

NOTE: this does not take Holidays into account.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Gary,

Thanks for your help. Unfortunately, running into an error
message when I try to run the query with the new
expression.

Run-time error '94'
Invalid use of Null

When I click the Debug button, the following line is
highlighted.

NumWeeks = DateDiff("ww", BegDate, EndDate)

Any suggestions? Thanks again for taking the time to
respond.
-----Original Message-----

Trying to create a query which will perform the following
function.

Take a date field [Date received] and another date field
[Date resolved]. Generate an average of how long it takes
to resolve. Here's the expression I'm using.

AverageDate: Avg([Date resolved]-[Date reported])

This is working fine, but now I need to add in one more
feature. I want to know only the average number of
weekdays (Monday through Friday) that it takes. Is this
possible?
Click to expand...

Hi Jeff,

Save the following function in a Module
(if this will be a new module, do not name
the new module the same as function name)

Public Function DateDiffW(BegDate, EndDate)
'adapted from http://support.microsoft.com/?kbid=288194
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)

'tacked on " +1" at end of following formula
'to count "up to and including" EndDate;
'Also, if same working day, will return 1,
'but, if same non-workingday, will return 0;

DateDiffW = NumWeeks * 5 + _
Weekday(EndDate) - Weekday(BegDate) + 1
End If
End Function

Then you might use in your query:

AverageWorkDays: Avg(DateDiffW([Date reported],[Date resolved]))

NOTE: this does not take Holidays into account.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter


.
Click to expand...
 
Hi Jeff,

Do you want to consider the case
where ResolvedDate is Null (hasn't
been resolved) to be a zero difference?

Or maybe I'm just *assuming* that's the
only field that will be Null. Could the
DateReported field also be Null?

One option would be to set Criteria
for ResolvedDate to Is Not Null, but
maybe you need those records for
some other aggregate?

or try this further adaptation (returns 0
if either is Null):

Public Function DateDiffW(BegDate, EndDate)
'adapted from http://support.microsoft.com/?kbid=288194
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

'if either is Null, this If/Then = False,
'so will return 0
If EndDate>BegDate Then
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)

'tacked on " +1" at end of following formula
'to count "up to and including" EndDate;
'Also, if same working day, will return 1,
'but, if same non-workingday, will return 0;

DateDiffW = NumWeeks * 5 + _
Weekday(EndDate) - Weekday(BegDate) + 1
Else
DateDiffW = 0
End If
End Function

Good luck,

Gary Walter
 
Public Function DateDiffW(BegDate, EndDate)
'adapted from http://support.microsoft.com/?kbid=288194
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

'if either is Null, this If/Then = False,
'so will return 0
If EndDate >= BegDate Then
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)

'tacked on " +1" at end of following formula
'to count "up to and including" EndDate;
'Also, if same working day, will return 1,
'but, if same non-workingday, will return 0;

DateDiffW = NumWeeks * 5 + _
Weekday(EndDate) - Weekday(BegDate) + 1
Else
DateDiffW = 0
End If
End Function
 
technically, line

'if either is Null, this If/Then = False,

should be

'if either is Null, this If/Then <> True,
 
Gary,

Thanks again for your help and sorry for the delayed
response over the weekend.

I'd prefer the query to ignore records with a null Date
Resolved field. I've set a criteria of "IsNotNull" for
Date Resolved and now I'm getting a "Data type mismatch in
criteria expression.
 
Hi Jeff,

There is a space between the words:

Is <space> Not <space> Null

Good luck,

Gary Walter
 
It's always the small stuff, isn't it. Thanks for your
help Gary - everything's running smooth now!
 
Back
Top