Help with time arithmetic in Access

February 5, 2006 at 00:28:48
Specs: Windows XP, na

I am putting together an Access data base to help streamline my office.

It is a catering company so the tables I have created are EventInformation, PayGrades, EmployeeInformation, and TimeSheet. Right now I am trying to setup a Query to calculate "hours" based off the "clockin" and "clockout" fields entered under the TimeSheet table so then I can multiply it by the hourly rate. How do I set this up? What if the clockout time is on the proceeding day?

Help is greatly appreciated.


See More: Help with time arithmetic in Access

Report •


#1
February 5, 2006 at 03:08:28

Normal calculating time differences is simple a matter of subtracting one from the other, that is providing the fields are defined as Time/Date fields. Time/Date are stored as floating point numbers.

Providing the clock on time and clock of time are in the same 24 hour period, then there is no problem. Time calculations completely ignore the date.

Problems can arise when the clock-in time and clock-out time span midnight - that is they occur on two different days.

In that is likely to happen you first need to determine if out time is less than in time. If it is, then there is a midnight in the middle.

You will then need to take the difference between in and out and subtract the sum from 24, or 23 hours, 59 minutes and 59 seconds to give you the correct number. 23 hours, 59 minutes and 59 seconds = 0.999988425925926 = the number of ticks since the previous midnight.


Stuart


Report •

#2
February 5, 2006 at 10:34:16

I am assuming I would have to set up an IF statement when "Timein">"Timeout". THEN 2400-([Timein]+Timeout])?

How do I set that up?

Is there anyway to have a user select a date off a mini calander when entering the time? It is a shot in the dark.


Report •

#3
February 5, 2006 at 10:53:54

Not quite

If TimeIn > TimeOut then
time = 23:59:59 - (timein-timeOut)
End if

So is someone clocks in at 22:00 and clock out at 06:00 the following morning they have done an eight hour shift.

22:00 - 06:00 = 16 hours. Not what you want.
However. 24 hours-16 = 8 hours, which is what you want.

You have to use 23:59:59, one second before midnight. 24 hours will equate to midnight which is Zero.

You can select the date using the Date Time picker Active X control

Stuart


Report •

Related Solutions

#4
February 5, 2006 at 12:00:31

How do I use the date time picker Active X control? Sorry not an Access stud yet.

Thanks again


Report •


Ask Question