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?

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

• ###### 1. Re: 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?

You are probably going to have to create a Shift ID (employee ID plus day plus first entry time) that is the same for all punches for the same shift. You can do this based on the time difference between the current entry punch and the the last exit punch (more than 6 hours, it's probably a new shift). You can absolutely do this in Tableau with table calcs, but honestly it's easier to do it in Excel is that's your data source.

• ###### 2. Re: 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?

I too have the same type of requirement.

Can i have the code for monitoring the day time shifts.

Thanks and Regards

Raviteja