Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 550

# How to calculate a "turn around time" value counting only workdays.

I have to calculate an issue end date based on a table showing a select number of topics which have a specific number of days allotted for issue resolution.  Table Example:
InquirySource      CompletionRequirement

Executive Request                    15
Internal Request                              7
External Request                        7
DCPSC                                      7
I'm trying to use the table above to calculate a project due date. (The date the issue was received  plus the completion requirement in business days would = the project due date) I used this formula to calculate the due date: Expr1: [T-Inquiry_Source]!CompletionRequirement+NAVIssues!Date_Rcvd]which
returns: InquirySource      CompletionRequirement     Date_Rcvd      Due Date
DCPSC                               7                             5/19/2006      5/26/2006
This works, but I was asked to make the "7"  reflect only week days.  Is there an easy way to calculate?
0
Elizholliday
• 3
• 2
• 2
• +1
1 Solution

Database DeveloperCommented:
There's a MS article that can give you a head start
http://support.microsoft.com/?kbid=290152

Which uses (I think)

Function OfficeClosed(TheDate) As Integer

OfficeClosed = False

' Test for Saturday or Sunday.
If WeekDay(TheDate) = 1 Or WeekDay(TheDate) = 7 Then
OfficeClosed = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
OfficeClosed = True
End If

End Function

And if you then combine that with this

Public Function fGetWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Dim dtmWalk As Date
Dim intLoop As Integer

dtmWalk = dtmStart

Do Until dtmWalk > dtmEnd
If Not OfficeClosed(dtmWalk) Then
intLoop = intLoop + 1
End If
Loop

fGetWorkDays = intLoop

End Function

Then that might get you there :-)
0

Commented:

0

Database DeveloperCommented:
Hmm and it appears I gave you the wrong function.  Don't know why I thought days were required.
Oh well - You'd need this flavour :-)

Public Function fGetEndDate(dtmStart As Date, intSpan As Integer) As Date

Dim dtmWalk As Date
Dim intLoop As Integer

dtmWalk = dtmStart
intLoop = 1
Do Until intLoop = intSpan
If Not OfficeClosed(dtmWalk) Then
intLoop = intLoop + 1
End If
Loop

fGetEndDate = dtmWalk

End Function
0

Accountant/DeveloperCommented:
I just posted another solution for calculating WorkDays ...  A little different than your request though.  He was trying to calculate the number of workdays and you are looking for the Due Date falling on a work day.

Try this.... Create this function and use it in your query.  This function will add one day to the Date_Rcvd if it's a weekday, 2 days to the Date_Rcvd if it's a Saturday and 1 day to the Date_Rcvd until the CompletionRequirement goes to 0.

Function CalcDueDate(varCompReq as Integer, varDateRcvd as Date) As Date

Dim varDate As Date
Dim varRespond As Integer

varDate = varDateRcvd
varRespond = varCompReq

If varRespond > 0 Then
Do Until varRespond = 0

varDate = varDate + 1
If DatePart("w", varDate) = 7 Then
varDate = varDate + 2
End If
If DatePart("W", varDate) = 1 Then
varDate = varDate + 1
End If

varRespond = varRespond - 1
Loop
End If

CalcDueDate = varDate

End Function

Your Query should look something like the following ...

Due_Date: CalcDueDate([CompletionRequirement],[Date_Rcvd])

ET
0

Accountant/DeveloperCommented:
Typo ...

"and 1 day to the Date_Rcvd"

"and 1 day to the Date_Rcvd if it's a Sunday"

ET
0

Commented:
Sorry:  If you replace Date() with Date_Recd and 7 with CompletionRequirement you will get there.
0

Commented:
As you can see, without data, I had to work with what I had, and Date() sprung to mind;-)
0

Author Commented:
Thank you everybody.  I found the article referenced in the first reply to be very useful.
0

Author Commented: