overnight time calclulation

[dekutoski]

How do I get a datedif function to take into account an overnight end time. I need to calulate hours worked, some employees come in at say 5P but leave at 1A the next day. I'm getting negative numbers on the calculation. PLEASE HELP!

 

[Remou]

I don't quite see the problem:

datediff("n",#12/16/2007 17:00#,#12/17/2007 01:00#)

 

[dekutoski]

if an employee clocked in @ 5P one day and clocks out @ 2 AM the next day in a RECORD.

It'll say start time: StartTime: 5PM
EndTime: 2AM (this record is attached to the previous days date) it should say 9 hours worked.

Please help

 

[Remou]

n=minutes
h=hours

datediff("h",#12/16/2007 17:00#,#12/17/2007 02:00#)

 

[Remou]

You do not show the names of the fields. It should be simple enough to substitute the names of fields. If you are having difficulties, you will find the DateDiff Function in help.

 

[lespaul]

ok so replace a valid date time field here:

datediff("h",#start date time field#,#end date time field#)

now if you have a SINGLE record like :
[tt]
EmpID WorkDate TimeIn TimeOut
1 12/15/2007 5:00 pm 2:00 am
[/tt]

then you will need to do a VBA module (or a REALLY UGLY query with lots of nested iifs) that checks to see if the time out is past midnight and increment the workDate to use as the end date time field.....

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PaulBricker]

If you do not have date values in your field or times that extend beyond 24 hours(without the dates, you won't anyway) then you can use an expression like this in the query to simply calculate the time difference.

TotalTime:Format(1+EndTime - StartTime,"hh:nn")

That will handle the times whether the times are on the same day or different days.

Paul

 

[AlexCuse]

Try this out.

DateDiff("h", TimeIn, TimeOut + 1) mod 24 as GoodTime

TimeOut + 1 : Adds 1 day to the TimeOut value

So you end up with (for your example record)

TimeIn TimeOut + 1
1/1/1900 5:00 PM 1/2/1900 2:00 AM

which is what you want.

The beauty off this is the MOD function, which will return the remainder of a division operation.

So, say you are 9 am to 5 pm:
TimeIn TimeOut + 1
1/1/1900 5:00 PM 1/2/1900 5:00 PM

Using DateDiff, you get 32 hours. But by using the MOD function, we get only the remainder which is 8 (and correct).

The only thing you need to worry about with this is the potential for employees to work 3 days in a row, or something. Although the way your data is stored does not really allow for this anyway, so you should be good.

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page