Calculating Difference Between 2 Dates but only for Workdays 2

[stazza18]

I have a piece of code that I use to get the turnaround time from when a problem is received to when it is completed. The code works fine in a form, but I can't translate it into just a query. Does anyone have any suggestions?

 

[FoxProProgrammer]

Please post the code that you are using to calculate the date difference. I could come up with my own, but you might want to use your own.

dz
dzaccess@yahoo.com

 

[stazza18]

This is the function I use. I run this code when the one date field loses focus, but I wasn't sure if you can call a function in a query.

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

 

[lespaul]

So if you try this in a query it doesn't work?

SELECT BegDateField, EndDateField, Work_Days(BegDateField, EndDateField) As DaysWorked FROM tblName

Leslie

 

[FoxProProgrammer]

stazza,

I must have been coding a function while you were typing. ) I haven't tried yours, but here's mine in case you want to use it.

Function numWorkDays(StartDate As Date, EndDate As Date) As Integer

' This function calculates the number of workdays between StartDate and EndDate
' The work week is defined as Monday through Friday.
' The function does not exclude holidays.

Dim i As Date

For i = StartDate To EndDate
    If (Weekday(i, 1) <> 1) And (Weekday(i, 1) <> 7) Then
        numWorkDays = numWorkDays + 1
    End If
Next i

End Function

You can call a UDF in a Query. Here is an example of how to call your function:

SELECT Work_Days([Begdate], [Enddate]) As NumWorkDays, ... FROM yourtable...

To call my function:

SELECT numWorkDays([startdate], [enddate]) As NumWorkDays, ... FROM yourtable...

dz
dzaccess@yahoo.com

 

[PHV]

An UDF must be declared as public in a standard code module, not a form module.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[stazza18]

That worked perfectly. I kept running into problems getting that too work. Thanks a bunch.

 

[MichaelRed]

could (alternatively), just search these fora for the subject. Should find several to numerous references to a faq as well as already existing soloutions, some of which also consider holiday dates as well as just the weekends.





MichaelRed