Calculating time

[Guest]

I have a datbase that tracks timein/ timout for employees. I have a time in
field and a time out field. I have been asked to add a field that calculates
a persons hours work for that day. How is the simplest way to add this?
Shifts can rotate, meaning a person may report on monday and 5PM and not
leave till Tuesday at 3am. time is formated to 24 hour time at the moment.

 

[DL]

Below is what I use, there are probably more elegant solutions

Private Sub TimeOff_Exit(Cancel As Integer)

If Forms!Sales!TimeOff < Forms!Sales!TimeOn Then
Forms!Sales!TimeChge = (Forms!Sales!TimeOff + 24#) -
Forms!Sales!TimeOn
Else: Forms!Sales!TimeChge = Forms!Sales!TimeOff -
Forms!Sales!TimeOn
End If

End Sub

 

[John W. Vinson]

I have a datbase that tracks timein/ timout for employees. I have a time in
field and a time out field. I have been asked to add a field that calculates
a persons hours work for that day. How is the simplest way to add this?
Shifts can rotate, meaning a person may report on monday and 5PM and not
leave till Tuesday at 3am. time is formated to 24 hour time at the moment.

The simplest way would be to store both the date and time in the time in/time
out fields. Access stores dates as numbers - a count of days and fractions of
a day (times) since midnight, December 30, 1899. A pure time field (regardless
of format) is stored as a time on that long-ago day - that is, 6:00pm is
stored as 0.75 and corresponds to 6pm, December 30, 1899. If you store the
date and time in the field, then 4am October 31 is actually eight hours after
8pm October 30 rather than 16 hours before!

If you want hours and fractions of an hour, you can use the DateDiff()
function to calculate the minutes worked and divide by 60. With the date and
time it's simple:

DateDiff("n", [Time In], [Time Out]) / 60.

If you store only the time not the date you need to correct for times wrapping
around midnight; on the assumption that the time will never exceed 24 hours,
you can use

DateDiff("n", [Time In], [Time Out]) / 60. + IIF([Time In] < [Time Out], 24,
0)

to add 24 hours to the -16 that the expression will calculate on its own.

The "n" in the DateDiff function is for miNutes - "m" is Months.

John W. Vinson [MVP]

 

[DL]

I just new there had to be a more elegant solution

I have a datbase that tracks timein/ timout for employees. I have a time
in
field and a time out field. I have been asked to add a field that
calculates
a persons hours work for that day. How is the simplest way to add this?
Shifts can rotate, meaning a person may report on monday and 5PM and not
leave till Tuesday at 3am. time is formated to 24 hour time at the moment.

The simplest way would be to store both the date and time in the time
in/time
out fields. Access stores dates as numbers - a count of days and fractions
of
a day (times) since midnight, December 30, 1899. A pure time field
(regardless
of format) is stored as a time on that long-ago day - that is, 6:00pm is
stored as 0.75 and corresponds to 6pm, December 30, 1899. If you store the
date and time in the field, then 4am October 31 is actually eight hours
after
8pm October 30 rather than 16 hours before!

If you want hours and fractions of an hour, you can use the DateDiff()
function to calculate the minutes worked and divide by 60. With the date
and
time it's simple:

DateDiff("n", [Time In], [Time Out]) / 60.

If you store only the time not the date you need to correct for times
wrapping
around midnight; on the assumption that the time will never exceed 24
hours,
you can use

DateDiff("n", [Time In], [Time Out]) / 60. + IIF([Time In] < [Time Out],
24,
0)

to add 24 hours to the -16 that the expression will calculate on its own.

The "n" in the DateDiff function is for miNutes - "m" is Months.

John W. Vinson [MVP]