transform time logs

[shayraber]

hi all,

i have a log time table with employees clock-in and clock-out times.

i wish to convert it to a new structure which will show duration per day per amployee

instead of:

EmpID	Date	Time	Type
100	01-10-17	08:30	clock-in
100	01-10-17	17:45	clock-out
200	01-10-17	09:03	clock-in
200	01-10-17	18:33	clock-out
300	01-10-17	08:57	clock-in

to have:

EmpID	Date	duration (hours)
100	01-10-17	8.75
200	01-10-17	9.5

p;ease assist

[tresesco]

Like this may be?

Note: If your in and out times come from different dates, you have to consider that dates as well in the calculation. May be creating a timestamp field separately concatenating time and date fields together.

[shayraber]

hi, thanks for the quick reply!

looks like what i was looking for.

2 further questions, if i may:

(1) What if an employee clocked in and out few times on one day?

clock-in: 08:00

clock-out: 12:00

clock-in: 13:00

clock-out: 18:00

how do I handle this (in script, probably)?

(2) What about night shift employees? clocking-in on 21:30 (for example) and clocking out on 05:30 on the next day?

in this case i wish to show 2.5 working hours on the first day and 5.5 hours on the next day

regards,

Shay