Jose Saiz

Jose Saiz

  • 1.5k
  • 260
  • 102.6k

Looking for the best formula to calculate punched time

May 23 2014 6:30 PM

hi, guys I need help finding the best formula to calculate employee punched time

I have tried almost different way but it does not always work for instance

I have a table with 3 Columns with Multiple Data Rows

[EmpId as integer, Punched_In_Out as datetime, Status as varchar(10)]

DOE001, 2014-05-23 9:56:11, IN

DOE001, 2014-05-23 18:07:41, OUT

Worked Hours = (18:07-9:56) result = 8:51 but in reality the result should be 8:03

8 Hours and 3 Minutes

The above formula works for greats but not every time like the case presented above

I have used the DATEDIFF(min,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT))

but this rounds it up to 9 hours

also tried the MOD like this

DATEDIFF(min,MIN(PUNCHED_IN_OUT),MAX(PUNCHED_IN_OUT)) % 60

different result

Does any one know of the best formula to calculate punched time?

Please help this is driving me nuts

TIA






Answers (1)