How are time fields (Am PM) calculated? 3

[luccas]

I need to calculate times on a form (Employees clocking in and out). The purpose is to record daily employee hours. The fields are [In], [LunchOut], [LunchIn], [Out], [OtherIn], [OtherOut]. These are Date/Time fields. Using the AM/PM format I am unable to calculate the employees daily hours. How can this be handled?

This is what I have come up with that doesn’t work.
=((Nz([Out])-Nz([In]))+(Nz([LunchOut])-Nz([LunchIn]))-(Nz([OtherOut])-Nz([OtherIn])))

 

[MichaelRed]

The most straight forward approach would (perhaps) to use the "DateDiff" function.

The use of the "Nz" function suggests that you are already quite familiar with the issue of missing/incomplete data sets. However the simplistic application implies that you need to think through the implications some more.

In general, you should either disregard incomplete transaction pairs, or assign a meaningful value to the missing item. Consider, for example, the first term in your calculation. It implies that either [Out] or [In] could be null and that 0 is an acceptable substitute for the missing value. BUT. If the person actually checked in and was currently still "in", your calculation would "credit" item with a negative time. This would appear to be harsh treatment for someone who is still on the job?

Another brief review of the statement reveals that there are three pairs of values to calculate the "daily hours". The 'calculation' further seems to take the six values (three pairs) from a single record. This does not appear to be the best schema for the data set, as it promotes a "wide" record as opposed to the current preference for the "narrow" record, which could be accomplished with only three fields ([In], [Out], [Purpose]).


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[JerryDennison]

Michael makes an excellent point, but the "narrow" record could be taken a step further and not require a "punch in" and "punch out" on the same record(which limits data entry to one person at a time for the entire day). You could setup the table to record the punch time, punch type(either in or out), and purpose if needed. You then have greater control over the punches as you can check to see if a person is trying to punch out when they don't have a corresponding punch in and vice versa. This does make for a little more complex query building and calculation of times but is well worth it in terms of data entry flexibility.