2 Replies Latest reply on Oct 19, 2015 1:37 AM by RAVITEJA NERAVATI

    What should be the correct formula to calculate field FIRST SWIPE IN and LAST SWIPE OUT for night shift employees that is for ROLLING DAYS?

    Sneha Shrungarpawar

      Hello,

                      I am facing some issues while building the reports in tableau and I need the help to solve the same.

                      I want to calculate the working hours of employees who are working in night shift  that is working hours calculation for rolling days.
      For calculating working hours I am taking difference of  their first swipe in time and last swipe out time for that day.
      For first swipe in I am taking minimum(in time) and for last swipe out maximum(out time)  for that  particular day. Now this is working fine for the employees who are working in day shift( 9am to 6 pm)  but it is not giving correct output for employees who are working in night shift(11 pm to 8 am).
      Reason is that if employee comes at night at let’s say 11’O clock and goes out  on next day morning at 8’O clock. And between this period he may go out and come in for some breaks as per below.

      Employee           Day                                        Swipe Time        Swipe Status 
      Sneha                   20 Jan 2013                         11.00 pm              Entry
      Sneha                   20 Jan 2013                         11.30 pm              Exit
      Sneha                   20 Jan 2013                         11.45 pm              Entry
      Sneha                   21 Jan 2013                         2.00 am                Exit
      Sneha                   21 Jan 2013                         2.30 am                Entry
      Sneha                   21 Jan 2013                         8.00 am                Exit

      Sneha                   21 Jan 2013                         11.00 pm              Entry
      Sneha                   22 Jan 2013                         8.00 am                Exit


                     
      So for 20 Jan 2013 the last out will be wrong that is 11.30 pm and for 21 Jan 2013 first in will be wrong that is  2.30 am instead of 11 pm  according to our formula maximum(out time) and minimum(in time) for  that particular day.

      So I need the help to  build some formula for field  FIRST SWIPE  IN and LAST SWIPE OUT which will reflect the correct output for day shift as well as night shift.

      Thanks,
      Sneha