Help with if then statement

[Joyce]

I have a month end report that I need that reflects if
research cases are resolved within an established
turnaround time. All but two of my research case types
have a turn around time of 5 days so I actually calculate
using 7 to account for the 2 day weekend. I have two case
types that have a turnaround time of 2 days. I can use
the factor of 2 for cases received on Monday-Wed, however
I need to calculate using 4 for these case types received
on Thursday and Friday. Is there an "if/then" expression
that I could use to calcuate using 4 as a turnaround time
for dates that fall on the specific Thursday and Friday
dates. I've never done an if,then expression or VB code
for this. I need something that will calculate this

If CaseType1 and CaseType4 received on 01/08/2004 and
01/09/2004 and 01/15/15 and 01/16/2004 and 01/22/04 and
01/23/2004 and 01/29/2003 and 01/30/04 Then TurnAround=4

I was given this code, but this won't work for 3 day
weekends or holidays, so I thought it would be best to
actually but the dates in my code that I need to calculate
4 or sometimes 5 for 3 days weekends.

Dim intAddedDays As Integer
Select Case DatePart("w", CaseDate)
Case vbMonday to vbWednesday
intAddedDays = 0
Case Else
intAddedDays = 2
End Select
DaysForCase = 2 + intAddedDays


Currently my report has 4 fields, they are:

Date Received: =Count([DateReceived])

Date Resolved: =Count([DateResolved])

Resolved within TurnAround time: =Count(IIf(DateDiff("d",
[DateReceived],[DateResolved])<=[TurnaroundDays],"Q"))

Percent Resolved within Turnaround Goal:=[txtQuick]/
[txtAccountsResolved]

Thank you for your help.

 

[Bryan Reich [MSFT]]

Joyce,
The problem you have described (work-date spans that take into account
holidays) is a non-trivial one that people struggle to solve all the time. A
common solution (but by no means the only one people have come up with) is
to have a database table of known holidays that you keep updated as far into
the future as you need. Then, you can query whether certain days in your
calculation are in the table, and if they are, then you can ignore them just
as if they were weekends.
Hopefully that idea may be enough to get you started. It sounds like you're
fairly new to code, so it would also help you to perhaps look online for a
VBA reference or get a VBA book to get you started with your coding skills
because to fully implmenet the solution you are looking for you're going to
need more than 1 if statement, plus string concatination, plus knowledge of
the object model to run a query or SQL statement and process the result set,
etc. It's not extremely difficult code, but could seem overwhelming for a
first timer.
--
Bryan Reich
Microsoft Office
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

I have a month end report that I need that reflects if
research cases are resolved within an established
turnaround time. All but two of my research case types
have a turn around time of 5 days so I actually calculate
using 7 to account for the 2 day weekend. I have two case
types that have a turnaround time of 2 days. I can use
the factor of 2 for cases received on Monday-Wed, however
I need to calculate using 4 for these case types received
on Thursday and Friday. Is there an "if/then" expression
that I could use to calcuate using 4 as a turnaround time
for dates that fall on the specific Thursday and Friday
dates. I've never done an if,then expression or VB code
for this. I need something that will calculate this

If CaseType1 and CaseType4 received on 01/08/2004 and
01/09/2004 and 01/15/15 and 01/16/2004 and 01/22/04 and
01/23/2004 and 01/29/2003 and 01/30/04 Then TurnAround=4

I was given this code, but this won't work for 3 day
weekends or holidays, so I thought it would be best to
actually but the dates in my code that I need to calculate
4 or sometimes 5 for 3 days weekends.

Dim intAddedDays As Integer
Select Case DatePart("w", CaseDate)
Case vbMonday to vbWednesday
intAddedDays = 0
Case Else
intAddedDays = 2
End Select
DaysForCase = 2 + intAddedDays


Currently my report has 4 fields, they are:

Date Received: =Count([DateReceived])

Date Resolved: =Count([DateResolved])

Resolved within TurnAround time: =Count(IIf(DateDiff("d",
[DateReceived],[DateResolved])<=[TurnaroundDays],"Q"))

Percent Resolved within Turnaround Goal:=[txtQuick]/
[txtAccountsResolved]

Thank you for your help.