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

    Date and Time Calc


      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

            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:



              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.



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



              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])


                  MAKEDATETIME([Date],[End Time])



              End Date-Time:

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

                  [Start Date-Time]


                  [End Date-Time Intermediate]



              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.




                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:




                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
                  Hari Ankem

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

                  • 6. 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
                    • 7. 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