Printable View
Hi All,
I am using Access 2010. and in a query I need to calculate the time laps from start to end of a process.
I know ho to use the datediff function in query (Atime: DateDiff("h",[start time],[end time])) and this is working just great, but I now need to apply some logic to this calculation:
- I need to only count the time from 08:00 to 16:00
- I can not count any time if it is the weekend so Saturday and Sunday.
Any help would be appreciated.
Br.
Cezar
Create a second query. This query would look at whatever date field is in your data source, and using the DatePart, enter your criteria.
In your already created query, use the second query as its source.
Hi Mrojas
I am sorry but I am not sure that I understand what you would put in the second query and how you would from that get to find the time without calculating the time from 16:00 to 08:00 + also not the full weekend.Quote:
Originally Posted by mrojas
Would you be able to assist a little more?
Hi Mrojas,
I was not able to send PM, But I have made a link to the sample database you requested:
https://www.nortonzone.com/pickup/81...KisS3R&src=url
The results are:
22 hours
16 hours
5 hours and 3 minutes
Thanks for your time.
Cezar
I haven't tested this much so I've left debug outputs in place for you test yourself.
The ProcTime function calculates time in seconds.
Sample debug output...
*** calculating test1 start = 05/12/2013 07:15:22 end 09/12/2013 15:45:33
...from 05/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 28800 secs
...from 06/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 28800 secs
...not counting Saturday
...not counting Sunday
...from 09/12/2013 08:00:00 to 09/12/2013 15:45:33
+ 27933 secs
test1 = 85533 secs
The ProcTimeAsString function formats the ProcTime value to a string. such as
test1 = 2 days, 7 hours, 45 mins, 33 secs.
The functions only require start and end times, but you can pass in a value for ID if you like which can be whatever you like (persons name/employee number) and might be useful for testing/debugging or if you test with more than one record.
To call from a query use something like :
Expr1: ProcTimeAsString([starttime],[endtime],[id])
Code:Public Function ProcTimeAsString(ByVal sTime As Date, ByVal eTime As Date, Optional ByVal id As String) As String
Const fullday As Integer = 60 * 60 * 8, hr As Integer = 60 * 60, m As Integer = 60
Dim l As Long, days As Integer, hours As Integer, mins As Integer, t As Integer
l = ProcTime(sTime, eTime, id)
t = l Mod fullday
days = (l - t) / fullday
l = t
t = l Mod hr
hours = (l - t) / hr
l = t
t = l Mod m
mins = (l - t) / m
l = t
ProcTimeAsString = days & " days, " & hours & " hours, " & mins & " mins, " & l & " secs."
Debug.Print id & " = " & ProcTimeAsString
Debug.Print
End Function
Public Function ProcTime(ByVal sTime As Date, ByVal eTime As Date, Optional ByVal id As String, Optional recurs As Boolean) As Long
Const TBEGIN As Date = #8:00:00 AM#
Const TEND As Date = #4:00:00 PM#
If Not recurs Then
Debug.Print "*** calculating " & id & " start = " & sTime & " end " & eTime
End If
If eTime <= sTime Then
Debug.Print "!!! Start is after end time !!!"
Exit Function
End If
If Not recurs Then
'First pass through, check start/end dates
Select Case Weekday(sTime, vbMonday)
Case 1 To 5
Case Else
Debug.Print "...Start day is a " & WeekdayName(Weekday(sTime, vbMonday), False, vbMonday)
'set start to the next monday @ TBEGIN
sTime = DateValue(sTime) + (8 - Weekday(sTime, vbMonday)) + TBEGIN
If eTime <= sTime Then Exit Function
End Select
Select Case Weekday(eTime, vbMonday)
Case 1 To 5
Case Else
Debug.Print "...End day is a " & WeekdayName(Weekday(eTime, vbMonday), False, vbMonday)
'set end to the previous friday @ TEND
eTime = DateValue(eTime) + (5 - Weekday(eTime, vbMonday)) + TEND
If eTime <= sTime Then
Debug.Print "Exited - start = " & sTime & " end = " & eTime
Exit Function
End If
End Select
Else
'don't process w/e days on subsequent passes
Select Case Weekday(sTime, vbMonday)
Case 1 To 5
Case Else
Debug.Print "...not counting " & WeekdayName(Weekday(sTime, vbMonday), False, vbMonday)
ProcTime = ProcTime + ProcTime(sTime + 1, eTime, id, True)
Exit Function
End Select
End If
'remove time before start
If TimeValue(sTime) < TBEGIN Then sTime = DateValue(sTime) + TBEGIN
'calculate hours for one day at a time...
Dim diff As Date
Debug.Print "...from " & sTime & " to " & eTime
If DateValue(sTime) = DateValue(eTime) Then
'start/end time is in same day
'remove time after cut off
If TimeValue(eTime) > TEND Then
eTime = DateValue(eTime) + TEND
Debug.Print " end time set to " & eTime
End If
diff = eTime - sTime
ProcTime = (((Hour(diff) * 60) * 60) + Minute(diff) * 60) + Second(diff)
If recurs Then Debug.Print " + " & ProcTime & " secs"
Else
'start/end times are in different days
'get time for first day
diff = (DateValue(sTime) + TEND) - sTime
ProcTime = (((Hour(diff) * 60) * 60) + Minute(diff) * 60) + Second(diff)
Debug.Print " + " & ProcTime & " secs"
'calculate next day...
ProcTime = ProcTime + ProcTime(DateValue(sTime) + 1 + TBEGIN, eTime, id, True)
End If
If Not recurs Then Debug.Print id & " = " & ProcTime & " secs"
End Function
Hi Jonh
Thank you very much for this solution this works perfectly
BR.
Cezar