Datediff to calculate hours worked Weirding out

[lmac]

Hi, I'm using

Total Hours Worked: DateDiff("n",[Start Time],[End Time])/60

to calculate employee hours worked for the day. It works fine except for employees who work after 12midnight. I'm using 24hr format for Start & End time. This is what I get for someone who worked 3:00pm-4:00am.

Start Time 15:00
End Time 04:00
Total Hours Worked = -11.00 ???? (Why am I getting negative # when it should be 13.00 total hours)

Any help?

 

[Andrew Fergus]

Hi
This is happening because you are only looking at the times, not the dates and quite rightly, the end time is less than the start time. (I know this isn't really the case but this is what Access thinks).

There are 2 ways to get around this. The first is to correct the problem which would be to use fields in your datediff function that incorporate the date and time (e.g. StartDateTime and StartDateEnd - both would be General Date format).

The other (quick and dirty) way would be to make an adjustment in your formula, like this :

DateDiff("n",[Start Time],[End Time])/60+IIf([End Time]<[Start Time],24,0)

HTH, Andrew

 

[lmac]

Txs andrew, I used the quik & dirty way in order to keep my 24 hr format data entry field. It worked perfectly! Txs again.