Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
| Date | duration (hours) | |
---|---|---|---|
100 | 01-10-17 | 8.75 | |
200 | 01-10-17 | 9.5 |
p;ease assist
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.
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