Turnaround Time Query- Minus weekend days

[shortikell]

My company has a database where jobs are signed in, and the support staff signs them out, stamping the date when completed. I am trying to get a report together for their turnaround time. My only problem is trying to subtract out the weekend days. Could anyone help?

Thanks!

 

[RichO]

    Dim d     As Date
    Dim days  As Integer
    d = StartDate
    While d < EndDate
        days = days - (DatePart("w", d) > 1 And DatePart("w", d) < 7)
        d = DateAdd("d", 1, d)
    Wend

You can use this code to calculate the turn around time for an unbound text box on your report. The variable "days" will contain the result you want.

 

[shortikell]

    Dim d     As Date
    Dim days  As Integer
    d = StartDate
    While d < EndDate
        days = days - (DatePart("w", d) > 1 And DatePart("w", d) < 7)
        d = DateAdd("d", 1, d)
    Wend

You can use this code to calculate the turn around time for an unbound text box on your report. The variable "days" will contain the result you want.

Is there a way to use this in the query? It's a little over my head to create a module to update the data, but could I use an update query or set this into the select query?

 

[shortikell]

Download the useful date functions db from the samples section. It has a business days function that also eliminates holidays.

The only sample db I have is the Northwinds one, and I looked through it to find a date function, but without any luck. Am I looking in the wrong place?

 

[RichO]

If you can't find the date samples, you can use the code I supplied as a function that you can use in a query:

First, copy the code below onto the clipboard.

From the database window, go into modules, select New. When the window opens up, paste the code into it.

Public Function TurnaroundTime(StartDate As Date, EndDate As Date)
    Dim d     As Date
    d = StartDate
    While d < EndDate
        TurnaroundTime = TurnaroundTime - (DatePart("w", d) > 1 And DatePart("w", d) < 7)
        d = DateAdd("d", 1, d)
    Wend
End Function

Press Ctrl+S to save your module. Name it whatever you want, or just the default name is fine.

Close out of the module and go to your query in design view. Go to a new row and in the field box enter:

=TurnaroundTime(StartDate, EndDate)

Now this is assuming your start and end date fields are named as such. If not, you will have to change them accordingly (but ONLY in the above expression, not in the module code).

When you view your query, the new field will show the calculated days for turnaround time. If you can get your hands on the samples Pat spoke of, that would a better idea so you can skip holidays as well. Until then, this should work for you.

Good luck

 

[raskew]

Not every country celebrates Saturday and Sunday as the weekend, e.g.,
Israel. What's your generic solution?

Bob