Time Calculation Days & Hours

[CCRT]

I'm trying to figure out why the first record returns a value of 2 days given the start and end times are less than 7 hours apart?

Additionally, Is there an easy way to caluculate the exact hours?

Fuction used in query results below:
ComplaintSentDate CustContactedDate ResponseTimeDays
8/4/2005 9:50:22 AM 8/4/2005 3:00:22 PM 2
8/23/2005 1:07:53 PM 8/23/2005 1:09:43 PM 1
8/26/2005 2:05:29 PM 8/29/2005 9:15:36 AM 1


ResponseTimeDays()
Thanks to MichaelRed's Code below

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    'Dim rstHolidays As Recordset
    Dim rstHolidays As DAO.Recordset
    
    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (Weekday(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
               rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                 Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

Thanks for any help
--Dave

http://home.comcast.net/~ccrt-racing/ccrt.htm

 

[lupins46]

The problem is due to the use of CLng() to convert the dates to long integeres. This introduces a rounding error.

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As Database
'Dim rstHolidays As Recordset
Dim rstHolidays As DAO.Recordset

Dim Idx As Long, idx2 As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date") '

Idx = Int(StartDate)
idx2 = Int(EndDate)
Do Until Idx >= idx2
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1
Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)
Idx = Idx + 1
Loop

DeltaDays = NumDays

End Function

 

[CCRT]

Thanks--

That did the trick.

Anyway to get the hours?

--Dave

http://home.comcast.net/~ccrt-racing/ccrt.htm