Solved Payroll Query (1 Viewer)

[Pat Hartman]

datediff("n",starttime,endtime-(endtime<starttime))

@CJ_London Can you please explain the logic in English or is there a typo?

 

[CJ_London]

if end time is less than start time it has to be the following day

either endtime<starttime or it's not. If it is less that starttime, it's true which is -1

so if end time is less that start time you have endtime--1=endtime+1

in minutes
?datediff("n",#19:03:45#,#23:00:00#-(#23:00:00#<#19:03:45#))
237
?datediff("n",#19:03:45#,#01:00:00#-(#01:00:00#<#19:03:45#))
357

obviously if the person has worked over 24 hours then the answer will be wrong - which is why always better to include the date and not just the time

 

[slharman1]

Going live for testing with all employees tomorrow!
I want to give a big shout out to the forum and all the great help I keep receiving from this forum!
Pbaldy
Plog
Gasman
CJ_London
And I won’t forget are arnelp from other posts I’ve made, if I am forgetting anyone it is not because I do not appreciate it, you’ve all been so helpful!
I want want to say special thanks goes to Pat Hartman for getting me through this! Your name will be on my splash screen for providing the time/date functions.
thank you all!
I am sure this is not over but a great hurdle has been overtaken and I couldn’t have done it without you all.
Sincere thanks to all!

 

[Pat Hartman]

I asked because I wasn't getting the the correct value for elapsed hours. when the result was true.

 

[CJ_London]

perhaps that is down to rounding? - haven't checked but think datediff for hours works on the hours in the time - in this example 21-20

?datediff("h",#20:59:51#,#21:00:00#)
1
?datediff("h",#20:01:51#,#21:00:00#)
1

 

[Pat Hartman]

Use an example with date and time, not just time.

 

[CJ_London]

Use an example with date and time, not just time.

I did say 'if you only have the time available you can use something like'

If using datetime and not just time, you don't need the adjustment as you well know. I had the impression OP was only recording the time since they seemed to have a problem calculating duration when the time was following day.

 

[Pat Hartman]

Too subtle for me. It wasn't clear that it would ONLY work if you had only time. Thanks.

 

[slharman1]

How do I put this code in a query OR
How can I

Private Sub Form_Current()
   
    If IsNull(txtAdjTimeIn) And Not IsNull(txtTimeIn) Then
       
        Dim t1 As Date
        Dim t2 As Date
        t2 = DateValue([txtTimeIn]) + TimeSerial(Hour([txtTimeIn]), Minute([txtTimeIn]), 0)
       
        t1 = TimeSerial(0, 15, 0)
       
            If Minute(t2) = 30 Or Minute(t2) = 15 Or Minute(t2) = 0 Or Minute(t2) = 45 Then
            [txtAdjTimeIn] = t2
            Else
            [txtAdjTimeIn] = RoundUpToNearestDate(t2, t1)
            End If
        'txtAdjTimeIm = roundDate(txtTimeIn, 3)
    End If
   
        If IsNull(txtAdjTimeOut) And Not IsNull(txtTimeOut) Then
       
        Dim t3 As Date
        Dim t4 As Date
        t4 = DateValue([txtTimeOut]) + TimeSerial(Hour([txtTimeOut]), Minute([txtTimeOut]), 0)
       
        t3 = TimeSerial(0, 15, 0)
       
        [txtAdjTimeOut] = RoundDnToNearestDate(t4, t3)
       
        'txtAdjTimeIm = roundDate(txtTimeIn, 3)
    End If
   

   
End Sub

 

[pbaldy]

You can't. You can create a public function that takes the 2 values as parameters and returns the result, and call that from a query.