4 Replies Latest reply on Dec 10, 2018 8:18 AM by joy.delosreyes

    Time Calculation


      Hi, I need help in calculating time difference. Not all employees have the same schedule - some are morning shift, night shift or mid shift.

      I need to know how to calculate:

      1. Time each employee is in different rooms per day/shift

      2. How many employees are in the Break room in same time per day/shift


      I also have different rooms:

      1. Break Room (Break room and Locker room. An employee might go here first to keep things)

      2. Door-In (Door before Prod)

      3. Prod (This will be the main door that will serve as the login)

      4. Room1 (Room inside Prod)

      5. Room2 (Room inside Prod)

      6. Server (Room inside Room2)

      7. Room3 (Room inside Prod)

      8. Door-Out (Main door that will serve as the logout. this is only the door with logout logs. Other rooms doesn't have logout logs)



      Thank you for your help!




      To all experts, please help!! Thank you so much!

      Zhouyi Zhang Mark Fraser Joe Oppelt Okechukwu Ossai

        • 1. Re: Time Calculation
          Don Wise

          Hi Joy,


          Please see attached 2018.3 workbook which partially addresses Question 2: How many employees are in Break Room at same time/hour. by date.


          However there aren't any time parameters given to determine SHIFT.


          You'll still be able to see all of the the underlying data which makes up the data point, by right clicking on the data point as shown below:

          Screen Shot 2018-11-27 at 12.59.32 PM.png

          However, for the first question...I don't know...much harder to do and answer that question in a good and reliable format due to the complexity of the number of rooms involved.  I see lot's of problems if using table calcs and summing of time isn't easy, especially when it crosses over a 24-hour period of time (i.e., Shifts).  I think the only way to answer your question by each room is through table calcs so it might be a very frustrating exercise to accomplish. 


          Just the same, if you can provide your parameters for what a shift would be, that would be a start.  Also, do you have an idea as to how you'd want the final outcome to look like?  It might help others to think about possible solutions and I'll think through some things myself...


          Thx, Don

          1 of 1 people found this helpful
          • 2. Re: Time Calculation

            Hi Don, thank you so much for your time and your help!

            I'm sorry for the confusion.


            2. How many employees are in the Break room in same time per day/shift

            I want to know the number of employees in the break room.

            For example:

            9:00 pm - 9:30 pm - 3 employees are in the break room

            6:00 am - 6: 30 am - 1 employee is in the break room



            1.  Time each employee is in different rooms per day/shift


            as employee 6 arrived, she may enter the break room to keep her bag in the locker.

            Then tap door-in >> tap Prod


            If Employee 6 is only part of the production, then she will not be able to enter any other rooms inside the production (Rooms 1-3)


            If Employee 1 is part of a particular dept, she may have access to Room 1.


            An employee may tap door out anytime, go to the breakroom, tap door-in then Prod.

            Another problem is, sometimes an employee forgot to tap door-out.




            Employee 6 (9:00 pm - 06:00 am sched)


            8:50 pm - Breakroom

            8:56pm -  Door in

            8:57 pm - Prod

            10:00 pm - door out

            10:05 pm - door in

            10:06 pm - Prod

            12:03 am - door out

            12:05 am = breakroom

            01:00 am - door in

            01:01 am - Prod

            04:00 am - door out

            04:01 am - breakroom

            04:15 am - door in

            04:16 am - Prod

            06:00 am - door out

            06:01 am - break room



            63 mins - prod (08:57 - 10:00)

            107 mins - prod (10:06 - 12:03)

            179 mins - prod (1:01 - 04:00)

            104 mins - prod (04:16 - 06:00)


            Total mins in prod = 453


            Shifts are:

            Night - 06:00 pm - 09:00 am

            Morning - 06:00 am - 10:00 pm

            Midshift - 11:00 am - 02:00 am


            Screen Shot 2018-11-27 at 4.33.03 PM.png


            I really appreciate your help, Don! Thank you.

            • 3. Re: Time Calculation
              Okechukwu Ossai

              Hi Joy,


              I'm getting in late on this one.


              The time calculation is possible only if the underlying dataset has the necessary fields to support the calculation logic. It seems there is no pattern to the way employees tap in and out of the rooms. Some rooms don't have logout logs and even where this is available, some employees didn't use them.


              From your previous post, there are 3 available shifts for employees as defined below;


              Night - 06:00 pm - 09:00 am

              Morning - 06:00 am - 10:00 pm

              Midshift - 11:00 am - 02:00 am


              I noticed a little inconsistency in the sample data. For example, Employee 6 on October 1, 2018 tapped in at 20:28:14 (8:28pm) and there is no record of when he left the building. The last log time is 23:43:36 (23:43pm) in the Prod room. 8:28pm to 23:43pm doesn't perfectly fit into any of the 3 shifts. Also on October 2nd, the earliest time record is 00:46am (an exit) and the latest time is 23:43pm (an entry). You can split this time into 2 periods; 00:46am to 06:00am and 20:46pm to 23:43pm. This also doesn't clearly fit into any shift. This will be a major problem when a shift spans across 2 days. Clear rules for employee shift definition and consistent time log data are required. I'm not sure your data is complete enough to answer the kind of questions you are asking it.


              Hope this helps.


              1 of 1 people found this helpful
              • 4. Re: Time Calculation

                Hi Ossai,

                Thank you for your response.


                the schedule of employee6 is from 09:00pm to 06:00am.

                For example, she will enter the office on December10, 09:00 pm and will leave on December 11, 06:00am.

                Lunch sched: 01:00 am to 02:00 am


                I am having a hard time figuring out how to create a statement that if an employee will enter the prod from 06:00pm to 09:00 am the next day, the shift is night shift.

                Then I need to calculate the total time the employee is in each rooms.

                06:00pm will be the minimum time for doorin then 09:00am will be the max for doorout.


                The same with other shifts.


                Hope you can help. Thank you so much