Round Time to Top of Hour if in Specific Range

[themurph2000]

I've seen similar threads for this before, but not ones that specifically meet my issue.

I have a Database that takes the scan of an employee's badge and marks the time by default. What I want to do is set up a field where, if the scan is made within 10 minutes before up to 20 minutes after the start of a shift time, that field will automatically place it at the shift start time; otherwise, it records the time the record was entered.

In other words, if someone was scanned in at 6:52 AM, the extra field would record 7AM, which is the start of the shift. Same if the scan was at 7:18 AM. But if it was at 7:32 AM, then 7:32 AM would be recorded.

Any thoughts? Anyone? Anyone? Bueller? Bueller?

 

[DCrake]

You need a function to do this

Publuc Function RoundTimes(AnyTime As String) As String

Dim H As Integer
Dim M as Integer

H = Val(Left(AnyTime,2)
M = Val(Mid(AnyTime,4,2))

If M >= 50 Then
   H = H +1
   RoundTimes = Format(H,"00") & ":00"
ElseIf M <=20 Then
   RoundTimes = Format(H,"00") & ":00"
Else
   RoundTimes = AnyTime
End If

End Function

Save the function in a standard module.

You can then examine the results by using

x = RoundTimes(<<[YourTimeFieldHere]>>)

David

 

[namliam]

Added problem david, I am guessing not every hour will have a shift starting, thus 6:52 might be 7:00 but 7:52 needs to be 7:52, while this function makes it 8:00

Also I would probably make the return a Date, instead of a string.

 

[DCrake]

To increase the functionality the function would need to be passed an argument to state which shift the time relates to. Like you say is the time late for one shift but early for another. That logic was not entered into in my example. It also does not content with anyone clocking in at 11:52 pm.

David

 

[themurph2000]

To increase the functionality the function would need to be passed an argument to state which shift the time relates to. Like you say is the time late for one shift but early for another. That logic was not entered into in my example. It also does not content with anyone clocking in at 11:52 pm.

David

Good catch. I was thinking along those lines already, since I can easily add a field that would convert the shift (First, Second, or Third) to the start time of said shift (7AM, 3PM, 11PM). I figured this would be necessary, since I'll have to reverse this process for the end of the shift (20 minutes before to 10 minutes after)

Thanks for the function!

 

[themurph2000]

To increase the functionality the function would need to be passed an argument to state which shift the time relates to. Like you say is the time late for one shift but early for another. That logic was not entered into in my example. It also does not content with anyone clocking in at 11:52 pm.

David

And actually, that's what I did. What I didn't realize was that having the time of the shift start (or end) meant I didn't have to make a long series of nested IIF statements; I only needed the one to check if it's in the range

OfficialTimeOut: IIf(DateDiff("n",[TimeOut],[ShiftTimeOut])<=20,IIf(DateDiff("n",[ShiftTimeOut],[TimeOut])<=10,[ShiftTimeOut],[TimeOut]),[TimeOut])

Thanks for everybody's help.

 

[namliam]

OfficialTimeOut: 
IIf(DateDiff("n",[TimeOut],[ShiftTimeOut])<=20
,IIf(DateDiff("n",[ShiftTimeOut],[TimeOut])<=10
 ,[ShiftTimeOut]
 ,[TimeOut])
,[TimeOut])

I dont think that is correct...
I.e. Shifttime: 7:00
TimeOut: 7:15

Will return 7:15 instead of the aimed for (I think) 7:00

I take it more like likely your looking for something like
IIF( DateDiff("n",[TimeOut],[ShiftTimeOut])< 20 and DateDiff("n",[TimeOut],[ShiftTimeOut]) > -10, [ShiftTimeOut] ,[TimeOut])

I thinkkkkk IF I understand your premisse correctly

 

[themurph2000]

I dont think that is correct...
I.e. Shifttime: 7:00
TimeOut: 7:15

Will return 7:15 instead of the aimed for (I think) 7:00

I take it more like likely your looking for something like
IIF( DateDiff("n",[TimeOut],[ShiftTimeOut])< 20 and DateDiff("n",[TimeOut],[ShiftTimeOut]) > -10, [ShiftTimeOut] ,[TimeOut])

I thinkkkkk IF I understand your premisse correctly

The code I put in was for the end of the shift, and the minutes are reversed. In other words, 20 minutes before to 10 minutes after instead of 10 before and 20 after for the start of the shift. It's working right in my query.

 

[namliam]

The code I put in was for the end of the shift, and the minutes are reversed. In other words, 20 minutes before to 10 minutes after instead of 10 before and 20 after for the start of the shift. It's working right in my query.

LOL erm ok me shut up now