need help with if then

  • Thread starter Thread starter Joyce
  • Start date Start date
J

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.



..
 
Joyce,

If I'm reading your post correctly then you appear to
have a design problem rather than a coding problem. What
I think you are trying to do is to calculate if a case is
resolved in a given number of WORKING days.

As you have said the number of working days in any week
can vary due to weekends, public holidays and other "non
working" events (and all of these will vary from country
to country).

Logically then the first step is to identify the non
working days and effectively ignore them in your
calculations. (Or conversely identify the working days
and use only these in your calculation).

After you have done this the coding is straightforward,
just base the calculation on the case type and the known
number of (working) days this type takes to complete.

I would never "Hard code" the working/non working days
values or the duration that a particular case type takes
in case these change over time (which they will). Keep
the values in tables which can be edited if required.

Note: The only time "hard coding" could be acceptable
would be for a report where all variables were known and
the report was to be run once only.

Hope this is of some help.

CJ



-----Original Message-----
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.



..


.
Click to expand...
 
Back
Top