Time Calculation of a record (1 Viewer)

[Waheed2008]

Hi,
I am working on the time attendance system for employees of a company and stuck into time calculations. The situation is that in a company there are three shifts:
1. 0700 hr to 1500 hr (NS-Normal Shift)
2. 1500 hr to 2100 hr (OT1-Overtime Shift 1)
3. 2100 hr to 0700 hr next day (OT2-Overtime Shift 2)
A table is keeping a record of IN & OUT movement of employees. E.g.

Table-1
ID = 114578
Date = Aug 5, 2012
Name = Kamran Khan
IN = 0900
OUT = 2200


If an employee Sign IN at 0900 hr and Sign OUT at 2200 hr, which will be one record in a table as shown above, how can I separate the time spent into the above three categories. I want to design a query where I can get NS, OT1 & OT2 from the record and show result similar like this:
ID = 114578
Date = Aug 5, 2012
Name = Kamran Khan
IN = 0900
OUT = 2200
Duration = 1300
NS = 0600
OT1 = 0600
OT2 = 0100


How can I design such a query. Please help me in this issue. I am trying for last couple of days but could not find any way of calculation.
Many many thanks in advance.
Regards
Abdul Waheed

 

[plog]

You would create a query to with calculated fields to break it down. Your calculations are going to be a little too much to do it on one line, so I suggest you make custom functions inside a module.

What are the field types of IN an OUT? How would the time 7:45 am be represented in your data? Would it be "0745" or "0775"? Are the last two characters of your time fields minutes or hundreths of an hour?

 

[Waheed2008]

Thanks for response. The field types are standard Time that can be either 12 hrs or 24 hrs formate. Time 7:45 will be represented either as 7:45 AM/PM or 09:45 (in 24 hrs formate if it is in the evening). The type is "Time".

How should I create the custom function and add it into query?

 

[plog]

Below is the code for a query and the function to calculate how much time a person spent on a Normal Shift:

 SELECT [Table-1].ID, [Table-1].[IN], [Table-1].OUT, getNStime([IN],[OUT]) AS NS
FROM [Table-1];

Below is the function code to paste into a module to calculate how much time was spent on a Normal shift:

Function getNStime(inTime, outTime)
    ' gets time spent on Normal Shift, i is time in, o is time out
ShiftStart = "07:00:00 AM"
ShiftEnd = "15:00:00 AM"
    ' start and end times of shift
If DateDiff("n", ShiftStart, inTime) < 0 Then inTime = ShiftStart
    ' if began working prior to shift start, sets time to beginning of shift
If DateDiff("n", ShiftEnd, outTime) > 0 Then outTime = ShiftEnd
    ' if worked after shift ended, sets time to end of shift
 
ret = DateDiff("n", inTime, outTime)
If ret < 0 Then ret = 0
    ' calculates time on shift, if less than 0 returns 0
 
getNStime = ret
End Function

Using that query and the function you should be able to copy and reconfigure it to work for all your different shifts. This will return total minutes.

 

[Waheed2008]

Hi,

Thanks for the help. I have tried the same and made a function in a module. Then I am using this SQL statement:

SELECT [AttTable].Name, [AttTable].EDate, [AttTable].LogInTime, [AttTable].LogOutTime, getNStime([LogInTime],[LogOutTime]) AS NS, *
FROM [AttTable];

But when I run this query, it gives me the following error:

Undefined Function 'getNStime' in Expression.

What is its reason and how to fix it up?

 

[Waheed2008]

Hi,
I have solved this issue. The module name & function name should not be same. I have changed name of function. But now the calculations done by DateDiff is very strange. Here is my function:

----------------------------------------------------------------
Public Function NStime(InTime, OutTime)

'Gets time spent on normal shift, 1 is LogIntime, 0 is LogOutTime
ShiftStart = "06:00:00 AM"
ShiftEnd = "14:30:00 AM"
'Start and end times of shift

If DateDiff("n", ShiftStart, InTime) < 0 Then InTime = ShiftStart
'If began working prior to ShiftStart, sets time to beginning of shift
If DateDiff("n", ShiftEnd, OutTime) > 0 Then OutTime = ShiftEnd
' If Worked after shift ended, sets time to end of shift

ret = DateDiff("n", InTime, OutTime)
If ret < 0 Then ret = 0
' calculates time of shift, if less then 0 returns 0
NStime = ret

End Function
-------------------------------------------------------------

The results are the following:
LogInTime = 8/5/2012 8:03:20 AM
LogOutTime=8/5/2012 3:03:29 PM
NS=0

If I disable If ret < 0 Then ret = 0 statement, here is the result:
NS= -59221053

I have tried to find out about the error. The problem seems to be in the following statement:

ret = DateDiff("n", InTime, OutTime)

The value stored in ret has some issue. Can you please guide me what is the problem?

Million Thanks in advance.

 

[plog]

ShiftEnd = "14:30:00 AM"

I don't think that's a valid time.

 

[Waheed2008]

Yes. You are right. ShiftStart and ShiftEnd are not valid time formats. When these values are used in DateDiff function, the results came wrong. I mean that ShiftStart cannot be subtracted from LogInTime which is actually a Date type field.

Now what to do in this situation? Is it possible that I convert ShiftStart (which is string) into Date/Time format? Or is there any way out? Please guide me. Thanks

 

[plog]

No, my point wasn't valid formatting, it was valid data. 14:30:00 AM does not exist. The same way 56:72:00 PM doesn't exist.

 

[Waheed2008]

I have also tried this:

ShiftStart = "6:00:00 AM"
ShiftEnd = "2:30:00 PM"

But it also does not work. What should be the values of ShiftStart & ShiftEnd?

 

[kipcliff]

You are including the date with the time in your arguments, but you are only setting a time in your constants. You could isolate the time component of your arguments into temporary variables, since the date part is not used for this function.

 

[kipcliff]

Here is the code I used:

Option Compare Database
Option Explicit
 
Public Function NStime(InTime As Date, OutTime As Date) As Date
 
Dim ShiftStart As Date, ShiftEnd As Date, ret As Date
Dim datIn As Date, datOut As Date
 
'Isolate time component of arguments
datIn = TimeSerial(Hour(InTime), Minute(InTime), Second(InTime))
datOut = TimeSerial(Hour(OutTime), Minute(OutTime), Second(OutTime))
 
'Gets time spent on normal shift, i is LogIntime, o is LogOutTime
ShiftStart = "06:00:00 AM"
ShiftEnd = "14:30:00 AM"
 
'Start and end times of shift
If DateDiff("n", ShiftStart, datIn) < 0 Then datIn = ShiftStart
'If began working prior to ShiftStart, sets time to beginning of shift
 
If DateDiff("n", ShiftEnd, datOut) > 0 Then datOut = ShiftEnd
' If Worked after shift ended, sets time to end of shift
 
ret = datOut - datIn
If ret < 0 Then ret = 0
' calculates time of shift, if less then 0 returns 0
NStime = ret
End Function

DateDiff returns a Long integer, not a Date. If you would rather have your duration expressed in decimal hours, you can keep the old assignment of ret, and just divide it by 60. Note: You would need to change the data type of ret and your function to Long.

 

[Waheed2008]

Than you very much. It worked for me. The was with component isolation.

 

[kipcliff]

I am happy it worked for you, but it occurred to me that it will not work for the overnight shift. You will need to go the other way and append the date component to your constants. Otherwise, the shift that spans midnight could have an end time less than the start time.

Option Compare Database
Option Explicit
 
Public Function NStime(InTime As Date, OutTime As Date) As Date
Dim ShiftStart As Date, ShiftEnd As Date, ret As Date
Dim datIn As Date, datOut As Date
Dim ShiftLength As Double
 
datIn = InTime
datOut = OutTime
 
'Gets time spent on normal shift, datIn is LogIntime, datOut is LogOutTime
ShiftStart = "06:00:00 AM"
ShiftStart = ShiftStart + DateSerial(Year(InTime), Month(InTime), Day(InTime))
 
'Duration of shift in minutes
ShiftLength = 8.5 * 60
 
'ShiftEnd = "14:30:00 AM"
ShiftEnd = DateAdd("n", ShiftLength, ShiftStart)
'Start and end times of shift
 
If DateDiff("n", ShiftStart, datIn) < 0 Then datIn = ShiftStart
'If began working prior to ShiftStart, sets time to beginning of shift
 
If DateDiff("n", ShiftEnd, datOut) > 0 Then datOut = ShiftEnd
' If Worked after shift ended, sets time to end of shift
 
ret = datOut - datIn
If ret < 0 Then ret = 0
' calculates time of shift, if less then 0 returns 0
 
NStime = TimeSerial(Hour(ret), Minute(ret), Second(ret))
End Function

This changed code provides a pattern that will work with all three shifts. Just change the constants as appropriate.