10 Replies Latest reply on Nov 28, 2018 12:57 PM by Chris Chalmers

    Date and Time Calc

    joy.delosreyes

      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!

        • 1. Re: Date and Time Calc
          Hari Ankem

          Can you please provide your data file and/or a packaged workbook (.twbx) with data extracted in it?

          • 2. Re: Date and Time Calc
            joy.delosreyes

            Hi Hari! Updated with attachment. Thank you. I'm looking forward for your help. Thanks

            • 3. Re: Date and Time Calc
              Hari Ankem

              Hope the numbers displayed below in minutes look right to you:

              1.png

               

              Here is what I did:

              1. I had to modify your data source to add a column named "Serial Number" which is simply a serial number as shown below. I had to sort your data by name and date and time before adding the above column.

              1.png

               

              2. I have joined the above data file with itself as shown below:

              1.png

               

              3. Created the following calculated fields:

              Start Date-Time:

                   MAKEDATETIME([Date],[Start Time])

               

              End Date-Time Intermediate:

              IF MAKEDATETIME([Date],[End Time])<[Start Date-Time] THEN

                  MAKEDATETIME(DATE(DATEADD("day",1,[Date])),[End Time])

              ELSE

                  MAKEDATETIME([Date],[End Time])

              END

               

              End Date-Time:

              IF DATEDIFF("hour",[Start Date-Time],[End Date-Time Intermediate])>4 THEN //assuming the shift is ending

                  [Start Date-Time]

              ELSE

                  [End Date-Time Intermediate]

              END

               

              Time Spent in Minutes:

              DATEDIFF("minute",[Start Date-Time],[End Date-Time])

               

               

              Hope this helps. The packaged workbook and the data file I have used is attached.

              1 of 1 people found this helpful
              • 4. Re: Date and Time Calc
                Chris Chalmers

                Hey Joy,

                 

                I think you're going to have trouble getting meaningful results from this data. There appear to be significant data integrity issues, some of which may be explained by employees tailgating through doors without scanning. For example, Employee 10 enters Prod at 15:44 on 10/12 and the next record is them entering Door-IN three days later. There must be a missing Door-OUT event in there. Employee 4 also seems to have closed the door to Room 2 at 14:03:50 on 10/29 without it ever having been opened.

                 

                These data integrity issues cause problems like employees in the dataset appearing to work for 50+ straight hours over weekends. There are probably other more subtle consequences that are hard to detect but will mess with aggregate results.

                 

                InOut_Timeline.PNG

                 

                On top of the data integrity issues, it is also impossible to know how long the employees spent in each room because there are no events for them leaving those rooms. For example, if someone enters the server room from room 2, we have no way of knowing when they go back to room 2. For that matter, we don't know when they leave from room 2 back to prod either. Assuming that someone leaves the server room when they enter room 1 creates inaccuracies by ignoring any time they spent in prod and room 2 in between. That kind of assumption also leads to employees being in the breakroom all weekend:

                 

                RoomTimeline.PNG

                 

                Specifically with the breakroom, there is no way to tell what someone did between Door-OUT and the following Door-IN, even if there is a breakroom entry event in between. They could have scanned their badge, gone in for a minute, and then left for an offsite lunch. They could have scanned their badge and stayed in the breakroom until the following Door-IN event. Those two possibilities are indistinguishable with this data, even ignoring the data integrity issues.

                 

                I think there are two possible ways to get the answers you want:

                • Get more detailed and reliable data, probably by using badge tracking beacons of some kind, or
                • Formalize significant assumptions about your data, and use them to get approximate answers.

                 

                For the second option, here are some assumptions you would have to decide on:

                • When an employee goes through a Door-OUT, Break Room, Door-IN cycle, assume that, if the time between Door-OUT and Door-IN is less than X minutes, they were in the breakroom the whole time.
                • When an employee enters Server Room and then enters Room 1, assume they spent X% of their time in Server Room, Y% of their time in Room 2, and Z% of their time in the area between Prod and the rooms. (And similar assumptions for other room transitions).
                • If a Door-OUT event, or any other event that presumes being inside that door, occurs after a Door-OUT event, assume there was a Door-IN event Y minutes before the second Door-OUT event. (and similar assumptions for other invalid transitions, like Door-IN to Door-IN and Room 1 to Server Room).

                 

                It's probably not the answer you wanted to hear, but I hope it helps a little. I've attached a workbook containing the visualizations I used to come to these conclusions. The timelines are created using a technique called Data Densification, also known as "Show Missing Values".

                 

                -Chris Chalmers

                1 of 1 people found this helpful
                • 5. Re: Date and Time Calc
                  joy.delosreyes

                  Hi Hari! Thank you soooo much for your time and for helping me!

                  Yes, you helped a lot. I actually got an idea on how this will work

                   

                  The problem that I have now is the shift of employees.

                  Since not all employees are in the morning shift.

                   

                  Some are mid and night shift.

                   

                  Morning shifts are between 06:00 am to 09:00 pm

                  Midshifts are between 10:00 am to 03:00am

                  Night shifts are between 06:00 pm to 09:00 am

                   

                  Employee 6 is night shift - schedule is 09:00 pm to 06:00 pm.

                  But of course we're allowed to stay earlier or late. so employee 6 might be in the prod from 08:00 pm to 07:00 am, etc.

                   

                   

                  Employee 1, midshift sometimes morning

                  Employee 4, night shift sometimes mid or morning

                  Employee 11 and 10 are morning - no problem with morning shifts

                   

                  Thank you so much Hari!  I hope you still have time to help me

                  • 6. Re: Date and Time Calc
                    Hari Ankem

                    I am not clear on your requirement here. Please clarify what's needed in specific.

                    • 7. Re: Date and Time Calc
                      joy.delosreyes

                      Hi Chris,

                      Yes, you got it right! You actually know how this works

                      That is really my problem with employees tailgating through doors.

                       

                      Thank you so much, Chris for sharing your thoughts. You helped a lot!

                      I will try to make my data complete to get a meaningful results.

                       

                      Your thoughts helped me identify the missing pieces of my data.

                      Thank you.

                       

                      But for now, I'm testing to check the time per day an employee is in different rooms.

                      Example: Employee 6

                       

                      DateTimeRoomCalculationActivityName
                      October 01, 201820:28:14Break RoomValid Card EntryEmployee6
                      October 01, 201820:41:02Break RoomValid Card EntryEmployee6
                      October 01, 201820:46:14Break RoomValid Card EntryEmployee6
                      October 01, 201820:49:59Door-IN0:21:45BreakroomValid Card EntryEmployee6
                      October 01, 201820:50:09Prod0:00:10Door-inValid Card EntryEmployee6
                      October 01, 201821:42:57Door-OUT0:52:48ProdValid Card ExitEmployee6
                      October 01, 201821:43:05Break Room0:00:08Door-outValid Card EntryEmployee6
                      October 01, 201821:50:25Door-IN0:07:20BreakroomValid Card EntryEmployee6
                      October 01, 201821:50:34Prod0:00:09Door-inValid Card EntryEmployee6
                      October 01, 201823:03:53Door-OUT1:13:19ProdValid Card ExitEmployee6
                      October 01, 201823:04:00Break Room0:00:07Door-outValid Card EntryEmployee6
                      October 01, 201823:08:58Door-IN0:04:58BreakroomValid Card EntryEmployee6
                      October 01, 201823:09:08Prod0:00:10Door-inValid Card EntryEmployee6
                      October 01, 201823:09:22Prod0:00:14ProdValid Card EntryEmployee6
                      October 01, 201823:30:06Door-OUT0:20:44ProdValid Card ExitEmployee6
                      October 01, 201823:30:14Break Room0:00:08Door-outValid Card EntryEmployee6
                      October 01, 201823:43:27Door-IN0:13:13BreakroomValid Card EntryEmployee6
                      October 01, 201823:43:36Prod0:00:09Door-inValid Card EntryEmployee6
                      October 02, 20180:46:05Door-OUT1:02:29ProdValid Card ExitEmployee6
                      October 02, 20180:48:34Door-IN0:02:29Door-OUTValid Card EntryEmployee6
                      October 02, 20180:48:45Prod0:00:11Door-INValid Card EntryEmployee6
                      October 02, 20181:00:17Door-OUT0:11:32ProdValid Card ExitEmployee6
                      October 02, 20181:00:28Break Room0:00:11Door-OUTValid Card EntryEmployee6
                      October 02, 20181:43:48Break Room0:43:20Break RoomValid Card EntryEmployee6
                      October 02, 20181:57:46Door-IN0:13:58Break RoomValid Card EntryEmployee6
                      October 02, 20181:58:48Prod0:01:02Door-INValid Card EntryEmployee6
                      October 02, 20182:32:55Door-OUT0:34:07ProdValid Card ExitEmployee6
                      October 02, 20182:33:04Break Room0:00:09Door-OUTValid Card EntryEmployee6
                      October 02, 20182:40:40Door-IN0:07:36Break RoomValid Card EntryEmployee6
                      October 02, 20182:40:50Prod0:00:10Door-INValid Card EntryEmployee6
                      October 02, 20183:28:37Door-OUT0:47:47ProdValid Card ExitEmployee6
                      October 02, 20183:30:15Door-IN0:01:38Door-OUTValid Card EntryEmployee6
                      October 02, 20183:30:26Prod0:00:11Door-INValid Card EntryEmployee6
                      October 02, 20184:30:26Door-OUT1:00:00ProdValid Card ExitEmployee6
                      October 02, 20184:30:34Break Room0:00:08Door-OUTValid Card EntryEmployee6
                      October 02, 20184:43:34Door-IN0:13:00Break RoomValid Card EntryEmployee6
                      October 02, 20184:43:45Prod0:00:11Door-INValid Card EntryEmployee6
                      October 02, 20184:52:03Door-OUT0:08:18ProdValid Card ExitEmployee6
                      October 02, 20184:52:09Break Room0:00:06Door-OUTValid Card EntryEmployee6
                      October 02, 20184:52:35Door-IN0:00:26Break RoomValid Card EntryEmployee6
                      October 02, 20184:52:43Prod0:00:08Door-INValid Card EntryEmployee6
                      October 02, 20185:41:50Door-OUT0:49:07ProdValid Card ExitEmployee6
                      October 02, 20185:41:57Break Room0:00:07Door-OUTValid Card EntryEmployee6
                      October 02, 20185:45:23Door-IN0:03:26Break RoomValid Card EntryEmployee6
                      October 02, 20185:45:35Prod0:00:12Door-INValid Card EntryEmployee6
                      October 02, 20186:00:07Door-OUT0:14:32ProdValid Card ExitEmployee6
                      October 02, 20186:00:14Break Room0:00:07Door-OUTValid Card EntryEmployee6

                       

                       

                      Calculation sorted by Rooms

                       

                      0:43:20Break Room
                      0:13:58Break Room
                      0:07:36Break Room
                      0:13:00Break Room
                      0:00:26Break Room
                      0:03:26Break Room
                      0:21:45Breakroom
                      0:07:20Breakroom
                      0:04:58Breakroom
                      0:13:13Breakroom
                      0:00:10Door-in
                      0:00:09Door-in
                      0:00:10Door-in
                      0:00:09Door-in
                      0:00:11Door-IN
                      0:01:02Door-IN
                      0:00:10Door-IN
                      0:00:11Door-IN
                      0:00:11Door-IN
                      0:00:08Door-IN
                      0:00:12Door-IN
                      0:00:08Door-out
                      0:00:07Door-out
                      0:00:08Door-out
                      0:02:29Door-OUT
                      0:00:11Door-OUT
                      0:00:09Door-OUT
                      0:01:38Door-OUT
                      0:00:08Door-OUT
                      0:00:06Door-OUT
                      0:00:07Door-OUT
                      0:00:07Door-OUT
                      0:52:48Prod
                      1:13:19Prod
                      0:00:14Prod
                      0:20:44Prod
                      1:02:29Prod
                      0:11:32Prod
                      0:34:07Prod
                      0:47:47Prod
                      1:00:00Prod
                      0:08:18Prod
                      0:49:07Prod
                      0:14:32Prod

                       

                      Date: October 1, 2018 (Shift: 09:00pm - 6:00 am)

                      129 minutes in Breakroom

                      2 mins Door in

                      5 mins door out

                      434 mins prod

                       

                      Thank you for your help, Chris!

                       

                       

                      • 8. Re: Date and Time Calc
                        Don Wise

                        Hi Joy,

                        I saw this was posted as a separate thread from the one I was working on, possibly duplicate threads, so I'll join in with my contribution to question #2 that you originally had.  Looks like Hari made headway with question #1.  I agree with Chris's assumption based on data found that there are integrity issues which led to several hours of results that weren't coming out right for me, so good on Hari for getting as far as he did !

                         

                        Please see below and attached for Question # 2: Number of Employees in Breakroom by hour (half hour increments) by shift.

                         

                        But...just now noticing in this post that the operational "shifts' don't match employee schedule...so there'll be an issue there with what you're trying to achieve...gave it a go just the same.

                        Screen Shot 2018-11-28 at 9.45.23 AM.png

                        1 of 1 people found this helpful
                        • 9. Re: Date and Time Calc
                          joy.delosreyes

                          I'm sorry about that, Hari.

                          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.

                           

                          Example: Employee 6

                          Example: Employee 6

                           

                          DateTimeRoomCalculationActivityName
                          October 01, 201820:28:14Break RoomValid Card EntryEmployee6
                          October 01, 201820:41:02Break RoomValid Card EntryEmployee6
                          October 01, 201820:46:14Break RoomValid Card EntryEmployee6
                          October 01, 201820:49:59Door-IN0:21:45BreakroomValid Card EntryEmployee6
                          October 01, 201820:50:09Prod0:00:10Door-inValid Card EntryEmployee6
                          October 01, 201821:42:57Door-OUT0:52:48ProdValid Card ExitEmployee6
                          October 01, 201821:43:05Break Room0:00:08Door-outValid Card EntryEmployee6
                          October 01, 201821:50:25Door-IN0:07:20BreakroomValid Card EntryEmployee6
                          October 01, 201821:50:34Prod0:00:09Door-inValid Card EntryEmployee6
                          October 01, 201823:03:53Door-OUT1:13:19ProdValid Card ExitEmployee6
                          October 01, 201823:04:00Break Room0:00:07Door-outValid Card EntryEmployee6
                          October 01, 201823:08:58Door-IN0:04:58BreakroomValid Card EntryEmployee6
                          October 01, 201823:09:08Prod0:00:10Door-inValid Card EntryEmployee6
                          October 01, 201823:09:22Prod0:00:14ProdValid Card EntryEmployee6
                          October 01, 201823:30:06Door-OUT0:20:44ProdValid Card ExitEmployee6
                          October 01, 201823:30:14Break Room0:00:08Door-outValid Card EntryEmployee6
                          October 01, 201823:43:27Door-IN0:13:13BreakroomValid Card EntryEmployee6
                          October 01, 201823:43:36Prod0:00:09Door-inValid Card EntryEmployee6
                          October 02, 20180:46:05Door-OUT1:02:29ProdValid Card ExitEmployee6
                          October 02, 20180:48:34Door-IN0:02:29Door-OUTValid Card EntryEmployee6
                          October 02, 20180:48:45Prod0:00:11Door-INValid Card EntryEmployee6
                          October 02, 20181:00:17Door-OUT0:11:32ProdValid Card ExitEmployee6
                          October 02, 20181:00:28Break Room0:00:11Door-OUTValid Card EntryEmployee6
                          October 02, 20181:43:48Break Room0:43:20Break RoomValid Card EntryEmployee6
                          October 02, 20181:57:46Door-IN0:13:58Break RoomValid Card EntryEmployee6
                          October 02, 20181:58:48Prod0:01:02Door-INValid Card EntryEmployee6
                          October 02, 20182:32:55Door-OUT0:34:07ProdValid Card ExitEmployee6
                          October 02, 20182:33:04Break Room0:00:09Door-OUTValid Card EntryEmployee6
                          October 02, 20182:40:40Door-IN0:07:36Break RoomValid Card EntryEmployee6
                          October 02, 20182:40:50Prod0:00:10Door-INValid Card EntryEmployee6
                          October 02, 20183:28:37Door-OUT0:47:47ProdValid Card ExitEmployee6
                          October 02, 20183:30:15Door-IN0:01:38Door-OUTValid Card EntryEmployee6
                          October 02, 20183:30:26Prod0:00:11Door-INValid Card EntryEmployee6
                          October 02, 20184:30:26Door-OUT1:00:00ProdValid Card ExitEmployee6
                          October 02, 20184:30:34Break Room0:00:08Door-OUTValid Card EntryEmployee6
                          October 02, 20184:43:34Door-IN0:13:00Break RoomValid Card EntryEmployee6
                          October 02, 20184:43:45Prod0:00:11Door-INValid Card EntryEmployee6
                          October 02, 20184:52:03Door-OUT0:08:18ProdValid Card ExitEmployee6
                          October 02, 20184:52:09Break Room0:00:06Door-OUTValid Card EntryEmployee6
                          October 02, 20184:52:35Door-IN0:00:26Break RoomValid Card EntryEmployee6
                          October 02, 20184:52:43Prod0:00:08Door-INValid Card EntryEmployee6
                          October 02, 20185:41:50Door-OUT0:49:07ProdValid Card ExitEmployee6
                          October 02, 20185:41:57Break Room0:00:07Door-OUTValid Card EntryEmployee6
                          October 02, 20185:45:23Door-IN0:03:26Break RoomValid Card EntryEmployee6
                          October 02, 20185:45:35Prod0:00:12Door-INValid Card EntryEmployee6
                          October 02, 20186:00:07Door-OUT0:14:32ProdValid Card ExitEmployee6
                          October 02, 20186:00:14Break Room0:00:07Door-OUTValid Card EntryEmployee6

                           

                           

                          Calculation sorted by Rooms

                           

                          0:43:20Break Room
                          0:13:58Break Room
                          0:07:36Break Room
                          0:13:00Break Room
                          0:00:26Break Room
                          0:03:26Break Room
                          0:21:45Breakroom
                          0:07:20Breakroom
                          0:04:58Breakroom
                          0:13:13Breakroom
                          0:00:10Door-in
                          0:00:09Door-in
                          0:00:10Door-in
                          0:00:09Door-in
                          0:00:11Door-IN
                          0:01:02Door-IN
                          0:00:10Door-IN
                          0:00:11Door-IN
                          0:00:11Door-IN
                          0:00:08Door-IN
                          0:00:12Door-IN
                          0:00:08Door-out
                          0:00:07Door-out
                          0:00:08Door-out
                          0:02:29Door-OUT
                          0:00:11Door-OUT
                          0:00:09Door-OUT
                          0:01:38Door-OUT
                          0:00:08Door-OUT
                          0:00:06Door-OUT
                          0:00:07Door-OUT
                          0:00:07Door-OUT
                          0:52:48Prod
                          1:13:19Prod
                          0:00:14Prod
                          0:20:44Prod
                          1:02:29Prod
                          0:11:32Prod
                          0:34:07Prod
                          0:47:47Prod
                          1:00:00Prod
                          0:08:18Prod
                          0:49:07Prod
                          0:14:32Prod

                           

                          Date: October 1, 2018 (Shift: 09:00pm - 6:00 am)

                          129 minutes in Breakroom

                          2 mins Door in

                          5 mins door out

                          434 mins prod

                           

                          Employee 6 shift starts at 09:00 pm (October 1)

                          ends at 06:00 am (October 2)

                           

                          Thank you, Hari

                          • 10. Re: Date and Time Calc
                            Chris Chalmers

                            Hey Joy,

                             

                            Just to re-emphasize something from my first reply with an example: with these five lines, your calculation tells you Employee 6 spent a total of 57 minutes in the break room. They likely spent significantly less than 57 minutes in the breakroom - it looks like they left Door-OUT, entered the breakroom, left and went somewhere else, came back, entered the breakroom, and then entered Door-IN. Because you assume they spent the whole time in the breakroom when they probably did not, the calculation will often greatly over-estimate how long employees spend in the breakroom. This same problem will also over-estimate time spent in each room, particularly the Server Room, and under-estimate time spent in Prod.

                             

                            October 02, 20181:00:17Door-OUT0:11:32ProdValid Card ExitEmployee6
                            October 02, 20181:00:28Break Room0:00:11Door-OUTValid Card EntryEmployee6
                            October 02, 20181:43:48Break Room0:43:20Break RoomValid Card EntryEmployee6
                            October 02, 20181:57:46Door-IN0:13:58Break RoomValid Card EntryEmployee6
                            October 02, 20181:58:48Prod0:01:02Door-INValid Card EntryEmployee6

                             

                            -Chris Chalmers