I Create table name tblattlogs
Create 5 columns:
logsid
UserId
INOUTMODE
DATE
TIME
INOUTMODE:
0=Time IN
1=Time OUT
logsid useid INOUTMODE date time
1 1 0 7/1/2017 8:00
2 1 1 7/1/2017 17:00
3 1 1 7/1/2017 17:10
4 1 0 7/2/2017 8:10
5 1 1 7/2/2017 17:00
6 2 0 7/1/2017 8:00
7 2 1 7/1/2017 17:00
8 2 0 7/2/2017 8:00
9 2 1 7/2/2017 16:10
As you can see in userid 1,date 7/1/2017 the user time out twice so i just want to calculate 8:00 to 17:10 totalHOurs=9:10, and if greater than 17:00 it will count as overtime else less than 17:00 it will count under time.and in userid 1,date 7/1/2017 the time IN=8:10 in will be status late.
i will set every user timecode 8:00 to 17:00.
RESULT:
Userid Date LoginTime LogoutTime TotalHours Totallate/Overtime status
1 7/1/2017 8:00 17:10 9:10 00:10 complete
1 7/2/2017 8:10 17:00 8:50 00:10 Late
1 7/1/2017 8:00 17:00 9:00 00:00 complete
2 7/2/2017 8:00 16:10 8:10 00:50 undertime
What I have tried:
I don't know what to do.I want to be the result like that and i don't know how to start the code.Please Help me with this problem. Thank You!
解决方案Which types are LoginTime and LogoutTime ?
If they are Date you can build a TimeSpan ...
Dim d1, d2 As Date
Dim d3 As TimeSpan
d3 = d1.Subtract(d2)