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
  • Last Modified:

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
Asked:
Elizholliday
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Leigh PurvisDatabase 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
    dtmWalk = DateAdd("d", 1, dtmWalk)
Loop

fGetWorkDays = intLoop

End Function

Then that might get you there :-)
0
 
GRayLCommented:
How about:

DueDate:  dateadd("d",datediff("w",date,dateadd("d",datediff("w",date(),dateadd("d",7,date())),dateadd("d",7,date()))),dateadd("d",datediff("w",date(),dateadd("d",7,date())),dateadd("d",7,date())))
0
 
Leigh PurvisDatabase 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
    dtmWalk = DateAdd("d", 1, dtmWalk)
    If Not OfficeClosed(dtmWalk) Then
        intLoop = intLoop + 1
    End If
Loop

fGetEndDate = dtmWalk

End Function
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Eric ShermanAccountant/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
 
Eric ShermanAccountant/DeveloperCommented:
Typo ...

"and 1 day to the Date_Rcvd"  

Should read ...

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

ET
0
 
GRayLCommented:
Sorry:  If you replace Date() with Date_Recd and 7 with CompletionRequirement you will get there.
0
 
GRayLCommented:
As you can see, without data, I had to work with what I had, and Date() sprung to mind;-)
0
 
ElizhollidayAuthor Commented:
Thank you everybody.  I found the article referenced in the first reply to be very useful.
0
 
ElizhollidayAuthor Commented:
And each of your solutions were very helpful as well.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now