Count difference between 2 dates

sdk development
1

I wanna know the difference in hours between two dates!

But I don’t wanna use 24h hours for a day, I wanna use the office working hours.

Monday : 8h
Tuesday : 8h
Wednesday : 8h
Thursday : 8h
Friday : 8h
Saturday : 2h
Sunday : 2h

Who can help me with this!

I use informix as database!

Listserv Archives (BOB member since 2002-06-25)

2

Marc,

I had to do the same thing, or similar. I had to calculate the duration of a system outage based on the scheduled hours of availability fo rthat system. So I created a table that contains the Start and Stop times for each system for each day of the week, then wrote the following VBA function to do the calculation.

Michael Welter
Sr. Technical Analyst
AirTouch

Function DurCalc(Market As String, Application As String, Instart As Variant, Inend As Variant)
'************************************************************************** ’ This function calculates the Supported Hours duration for outages. ’ It compares the start & end times of the outage with the Market/System ’ production support hours in the tblProductionHours Table in its computation ’ of duration. Input parms are Market, Application, Start time of duration, ’ and End time of duration.
'**************************************************************************

Dim Db As Database
Dim rstdata As Recordset

Set Db = CurrentDb
Set rstdata = Db.OpenRecordset(“tblProductionHours”, dbOpenDynaset)

Dim MarApp As String
Dim DayOfWeek As Integer
Dim Stime As Date
Dim Etime As Date
Dim StartTime As Date
Dim EndTime As Date
Dim Indate As Date

Indate = DateValue(Instart)
StartTime = CDate(Instart) - Indate
EndTime = CDate(Inend) - Indate
MarApp = Market & " " & Application
DayOfWeek = DatePart(“w”, Indate)

Select Case DayOfWeek
Case 1
Stime = dlookup("[Sunday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Sunday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 2
Stime = dlookup("[Monday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Monday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 3
Stime = dlookup("[Tuesday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Tuesday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 4
Stime = dlookup("[Wednesday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Wednesday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 5
Stime = dlookup("[Thursday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Thursday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 6
Stime = dlookup("[Friday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Friday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

Case 7
Stime = dlookup("[Saturday Start]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)
Etime = dlookup("[Saturday Stop]", “tblProductionHours”, “[Market/System] = '” & MarApp & “’”)

If StartTime < Stime Then
StartTime = Stime
End If

If EndTime > Etime Then
EndTime = Etime
End If

End Select

If VarType(Stime) = 1 Or VarType(Etime) = 1 Then
DurCalc = Null
Else
DurCalc = DateDiff(“N”, StartTime, EndTime) End If

If StartTime > Etime Or EndTime < StartTime Then
DurCalc = Null
End If

If DateDiff(“N”, StartTime, EndTime) <= 0 Then
DurCalc = Null
End If

End Function

Listserv Archives (BOB member since 2002-06-25)