Re: Employee Time Sheet Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I'll elaborate. The problem with the calculation is that by NOT capturing the Date as in #8/30/2005 1:07:00 PM# as opposed to #1:07:00 PM# is that you do not have a built in point of reference for the calculation. As such you're having to bend over backwards to determine if the TIME OUT ocurred after midnight and then adjust things acccordingly. Basically, Access stores Dates & Times as numbers. For example, 9/1/2005 8:50:12 AM is stored as 38596.3681944444 where the DATE is represented by the 38596 and the decimal is the time. The 38596 is the number of days since #12/31/1899# or #1/1/1900# (can never remember). When you capture the full date/time, its alot easier to determine if the shift crossed midnight or not. Among other things, by just capturing the time, you introduce the possibility that the person may have entered the wrong value.

Change the manner in which the person enters the TIME IN and TIME OUT to include the DATE. You might be able to do something where the user enters the time and then you programically attach TODAY'S DATE. If the TIME OUT is less than TIME IN then you subtract 1 from TODAY's Date. I would go with a situation where you have two bound fields for TIME IN and TIME OUT that display the full Date/Time. This fields would be locked. I would use two UNBOUND fields to capture the TIME IN and TIME OUT and then use code to set the values of the BOUND controls. The code would execute on the AfterUpdate event of the unbound controls. You may also want to add a warning/confirmation message when the value of TIME OUT is earlier than TIME IN to confirm that the shift spanned midnight. Of course, I would also only accept MILITARY time via an appropriate input mask. (Its easier to enter 17:00 as opposed to 5:00 PM).

Side note, if the DB already contains records, you'll probably want to do some work to update the TIME IN and TIME OUT to add the DATE to those preexisting values. This can be done via QUERIES or DAO. ***MAKE A BACKUP OF THE TABLE BEFORE TRYING THIS***

Ginzell wrote: 
I'm sorry, I'm a little lost on this still.
Do I put this in my function?  If so where?
And if not, where do I put it?  I'm just not sure what you mean
by capture the full date/time
Thanks

"David C. Holley" wrote:


Capture the full date/time of the time in/time out and then use the DateDiff() to perform the calculation. The examples below will return the total number of MINUTES the person worked. This will also have some benefits in that you'll be able to tie a specific shift back to a date or pay period and other whatnot*.

David H
*my new favorite word

?DateDiff("n", #8/30/2005 1:07:00PM#, #8/30/2005 10:00:00 PM#)


?DateDiff("n", #8/30/2005 11:07:00PM#, #8/31/2005 8:00:00 AM#)


Ginzell wrote:

I'm doing a Time In /Time out / Total for Employee's and I'm using the function below, but I'm not getting the right totals back.
I'm getting:
Time In Time Out Hours:Minutes
7:00:00PM 9:00:00AM -10 ---
Total Hours = -10
If I use regular hours - say, 9-5 it works, but when I get into graveyard shifts it messes up. Any help greatly appreciated.

Option Compare Database
Option Explicit

Public Function HoursAndMinutes(interval As Variant) As String

Dim totalhours As Long, totalminutes As Long, totalseconds As Long
Dim hours As Long, Minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

totalhours = Int(CSng(interval * 24))
hours = totalhours Mod 24

totalminutes = Int(CSng(interval * 1440))   ' 1440 = 24 hrs * 60 mins
Minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400))  ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then Minutes = Minutes + 1  ' round up the minutes and
If Minutes > 59 Then hours = hours + 1: Minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(Minutes, "00")


End Function


.

Relevant Pages

  • Re: Employee Time Sheet Question
    ... Capture the full date/time of the time in/time out and then use the DateDiffto perform the calculation. ... This will also have some benefits in that you'll be able to tie a specific shift back to a date ... but I'm not getting the right totals back. ... Dim totalhours As Long, totalminutes As Long, totalseconds As Long ...
    (microsoft.public.access.modulesdaovba)
  • Re: [BUG,2.6.28,s390] Fails to boot in Hercules S/390 emulator
    ... the negative difference between them into clock->error. ... What we are actually doing is storing the _remainder_ in xtime. ... problem with the calculation of clock->error. ... The calculated new error is correct given the shift. ...
    (Linux-Kernel)
  • BSCAN_SPARTAN3 proper use with CAPTURE and UPDATE
    ... "a little" difficult (instead less functional DRCKx signals are provided). ... the DRCKx does not work when CAPTURE or UPDATE are active, ... the 18-bit data register, BTW - either BSCAN... ... -- Load and shift data ...
    (comp.arch.fpga)
  • Re: Get info from changing cell to a static cell ??
    ... Private Sub Worksheet_Calculate ... Dim iRowOffset As Integer ... 'Shift old time values down 1 row ... anything that causes automatic calculation to occur, ...
    (microsoft.public.excel.programming)
  • Re: Round-Off Errors HP 48G
    ... Northing Easting ... the entire group of coordinates is "shifted" down to near the coordinate system origin for the purpose of the calculation. ... Now without the "shift", the answer is: ... You can see how the longer the list of high numbered coordinate values is, the larger a number \GSLIST will return, and with only 12 available digits you run out of digits pretty quickly. ...
    (comp.sys.hp48)