2 Replies Latest reply: Feb 21, 2018 10:55 AM by shay raber RSS

    transform time logs

    shay raber

      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:

      EmpIDDateTimeType
      10001-10-1708:30clock-in
      10001-10-1717:45clock-out
      20001-10-1709:03clock-in
      20001-10-1718:33clock-out
      30001-10-1708:57clock-in

      to have:

      EmpID
      Dateduration (hours)
      10001-10-178.75
      20001-10-179.5

       

      p;ease assist

        • Re: transform time logs
          Tresesco B

          Like this may be?

           

          Untitled.png

           

          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.

            • Re: transform time logs
              shay raber

              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