1 2 Previous Next 15 Replies Latest reply on Aug 25, 2018 5:03 PM by Okechukwu Ossai

    Calculating working hours for night shifts - find MIN(shift end time) after the shift start time

    Ivan Ivanov

      Hello everyone,

      I am new to the Tableau community and this is my first post. I first tried to find a solution in the forums but had no success.

       

      Every month I receive an Excel spreadsheet with check-in and check-out times of employees and I have to calculate their working hours for the day. I do so with this formula

       

      DATEDIFF('minute', MAX([Shift Start]), MIN([Shift End])) / 60

       

      and it works as long as the Shift Start and Shift End are in the same day (data discrepancies don't matter at the moment - employees are currently being taught how to use the system as they still check in and out multiple times during the day and we also have outs before the ins quite often). My problem is that I cannot sum the working hours for the night shifts because the shift start and shift end are in different days. As you can see in the attached workbook, the shift ends do not stay against their respective starts but against a NULL field instead; on the other hand, there are NULL values against all night shift starts. I suppose that if I manage to position them on the same line the desired calculation will be accomplished with ease.

      Photo example.png

       

      Another thought I have on my mind is that if I can calculate the time difference between MAX(Shift start) and the next Shift end entry for that employee (something like MIN(Shift end) > MAX(Shift start)) I will get the results I am hoping for. Probably a LOD calculation will do the work but I can't really think of a way to achieve it.

       

      Can anyone offer any help for this please? Any help is appreciated. Thank you for taking the time to read through my post!

       

      * I am attaching a Tableau packaged workbook in which I have filtered to 4 of the employees having night shifts. I am using a spreadsheet with sample shift records (400 entries). I have the workbook already filtered to 4 of the employees having night shifts.

        • 1. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
          Okechukwu Ossai

          What is the official start and end time for night and day shifts respectively?

          • 2. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
            Ivan Ivanov

            Hello Okechukwu Ossai, and thanks for joining me in solving this problem.

             

            A night shift is one that spans over two consecutive days, e.g. one that starts before 23:59 (11:59 PM) on a given day and ends after 00:00 on the following day. There are no official start and end times for night shifts as the stake per hour is all the same.

             

            Best wishes,

            Ivan

            • 3. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
              swaroop.gantela

              Ivan and Okechukwu,


              Sorry for jumping in. I saw this thread as something similar that

              I've been working on:

              Time Calc

               

              Just wanted to toss out a method, but it may not be the most optimal.

              Instead of using the Day of the Entry, I opted for conditionals to

              check if the current type is OUT and the Previous was IN, then

              calculate the difference.  Failing that, if the entry two back was

              an IN then calculated the difference:

              IF ATTR([Entry type]) = "OUT" AND  LOOKUP ( ATTR ( [Entry type] ), -1 )="IN"

              THEN DATEDIFF('minute', LOOKUP ( ATTR ( [Entry date] ), -1), ATTR ( [Entry date] ) ) / 60

              ELSEIF ATTR ( [Entry type] )="OUT" AND  LOOKUP ( ATTR ( [Entry type] ),-2 )="IN"

              THEN DATEDIFF('minute',LOOKUP (ATTR ( [Entry date] ) ,-2), ATTR ( [Entry date] ) )/60

              END

               

              This method is agnostic to the day crossing.

               

              The Table Calculation settings are shown below.

               

              This is just an example, but you can make your conditional more detailed to capture

              more possible cases (IN-IN-OUT, IN-OUT-OUT, etc).

               

              Please see workbook v10.3 attached in the Forum Thread.

              27039work.png

              1 of 1 people found this helpful
              • 4. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                Okechukwu Ossai

                Hi Ivan,

                 

                This one is very tricky. I took a different approach using LODs.

                My preference for LOD in this particular problem is that it allows more flexibility in the way the final results can be visualized. See attached workbook.

                 

                I divided the solution into several steps mainly due to the discrepancies in the data. The major objective is to create a Billing Date which positions a split night shift on the same row. Only afterwards can a final Shift Start and Shift End dates be calculated.

                 

                I'll leave you to go through the solution and let me know if you require further clarifications.

                 

                Hope this helps.

                Ossai

                 

                1 of 1 people found this helpful
                • 5. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                  Shinichiro Murakami

                  HI All,

                   

                  Sorry for jumping in, but providing the starting point.

                  Looks difficult but with using range join as data connections, it brings easier starting point.

                   

                   

                   

                   

                   

                   

                  Thanks,

                  Shin

                  1 of 1 people found this helpful
                  • 7. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                    Ivan Ivanov

                    Dear Ossai, Murakami and Gantela,

                     

                    Thank you for the solutions that you provided. I had the time to look through all of them and the one that's most applicable to our data is Ossai's solution as it is closest to the final visual that my company wants achieved.

                    However, I still have one problem that I cannot solve. It only appears when an employee ends their shift in the morning and begins the next shift in the evening of the same day. So Tableau assumes this is the same shift which begins on let's say 2018-08-21 at 10:15 PM (22:15) and ends on 2018-08-21 at 12:15 PM (12:15) with a duration of -10 hours. What I want to do is take the last clock-in of every day and associate it with the first clock-out after that entry. Honestly, I haven't got an idea how to do that.

                     

                    I am uploading Ossai's workbook with data added for the employee Test Testison who has the same problem with 2 of his entries: on the 31st of July there is a shift end (clock-out) at 7:46 AM and a shift start (clock-in) at 7:33 PM (19:33) which should not be associated together as they are part of 2 separate shifts. The shift that starts on the 31st of July ends on the 1st of August at 7:49 AM and these two entries should be connected in a single shift. The clock-in at 7:49 on the 1st of August is obviously a mistake by the employee and if taken into account it should appear with a NULL value at the shift end entry and 0 Daily Working Hours.

                     

                    photo1.png photo2.png

                    I have added a sheet called Final Results (2) which is what we want the table to look like at the end. Any help is appreciated! Thank you!

                    • 8. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                      Ivan Ivanov

                      Dear Gantela,

                       

                      I would like to provide some feedback particularly to your solution. While testing I figured out that if there 2 or more consecutive OUT entries, it was calculating the time difference between all of them and the last IN before them. So I replaced the formula in Hours Worked 2 with the following:

                       

                      IF ATTR([Entry type])="OUT" AND  LOOKUP(ATTR([Entry type]),-1)="IN"

                      THEN DATEDIFF('minute',LOOKUP(ATTR([Entry date]),-1),ATTR([Entry date]))/60

                      ELSE 0

                      END

                       

                      This way it only calculates the hours for the first clock-out for the day and returns 0 for all other clock-outs during the same day.

                       

                      I also noticed that this field doesn't work if I use it in Text under Marks. It only visualised properly if dragged to Rows but therefore, no Grand/Subtotals can be calculated. I haven't thought of how to work through this.

                       

                      Cheers for your help! I am sure this solution might help others on the forums.

                      • 9. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                        Okechukwu Ossai

                        Hi Ivan,

                         

                        The solution works if you feed it good data. The challenge is that your dataset is not very good and I suggest you clean it up before connecting to Tableau.

                         

                        The negative  working hour is actually 'correct' based on your data. The employee clocked in and out on 31/07/2018. However, he first clocked out before he clocked in. The data is probably missing a clock in on 30/07/2018.

                         

                        I didn't add extra logic to exclude such cases because I didn't expect that. There is so much logic you can add to a formula before it becomes too clumsy. The best approach will be to clean up the data.

                         

                        So on 31/07/2018, the employee closed work before he resumed; hours worked is negative.

                        The next day, on 01/08/2018, same employee resumed and closed exactly at the same time; hours worked is zero.

                         

                         

                         

                        It seems Test Testison worked night-shift for most of the days which is not being reflected in the calculation. This is because the first few entries are suspicious. I'm happy to check why the night-shifts of this employee are not considered but will rather do so with a cleaned up dataset, not just for Test Testison but other employees also.

                         

                        Is there a way to include a Billing Date field in your data collection? That will resolve the issue right from source.

                         

                        Ossai

                        • 10. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                          Ivan Ivanov

                          Hello again and thank you for your help!

                           

                          The data you have marked for Test Testison on 31.07.2018 is correct - his night shift ends at 07:46 and his next shift begins at 19:33. The latter ends at 07:49 the next morning.

                           

                          So I am still trying to connect the last clock-in for every day with the next clock-out regardless of the day. I will continue working through this and will provide a solution if I come up with one for my specific case. I also look forward to any suggestions in this regard.

                           

                          I will mark your first answer as correct as it helped me the most so far.

                           

                          Cheers everyone!

                           

                          Ivan

                          • 11. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                            Okechukwu Ossai

                            You're welcome Ivan. I'll see if I can modify the logic to capture that scenario.

                            • 12. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                              Ivan Ivanov

                              I've tried all of these but I couldn't make any of them work:

                               

                              Finding the Second Purchase Date with LOD Expressions (video - this covers exactly what I need and still no result)

                               

                              Level of detail - finding next date

                               

                              Check for First Occurrence

                               

                              Things Tableau — Find the # of days between the First Date and the Second date for a customer.

                               

                              I have taken the following approach: the billing date equals the shift start date including the times. I am not doing any complex calculations for the start: I don't care whether the clock-in is correct or not, I just take the last clock-in for the day and assume this is when the shift begins. The struggle is (once again said) assigning the shift end to the shift start. I am trying the following:

                               

                              Shift start -> { FIXED [Employee name], [Entry date] : MAX(IF [Entry type] = "IN" THEN [Entry date] END) }

                              Shift end -> { FIXED [Employee name] : MIN(IF [Entry date] > [Shift start] AND [Entry type] = "OUT" THEN [Entry date] END) } ( I want to have all shift ends for everyday and then only take the one that I need - the NEXT end after the start. )

                              First end after start -> { FIXED [Employee name] : MIN ( IF [Shift end] > [Shift start] THEN [Shift end] END ) }

                               

                              Obviosuly, the last two don't work .... I think that if they do I might not need the Billing date anymore and be able to only calculate the difference in hours between the Shift start and First end after start.  Sample workbook is attached. Any help is appreaciated.

                               

                              With my warmest regards,

                              Ivan

                              • 13. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                                Okechukwu Ossai

                                The approach you are using won't work because the IN and OUT records do not live on the same row. You need a Billing Date and to do that you still need to add logic to handle all data inconsistency. I took a different approach, I got it working to the end only to realize there's a major block.

                                 

                                 

                                I couldn't split Shift start and Shift end into separate calculated forms. I had this error message related to how LODs are stored in the datasource.

                                 

                                At least I'm almost there. I need to find time to fix this some time and get back to you later.

                                 

                                Ossai

                                • 14. Re: Calculating working hours for night shifts - find MIN(shift end time) after the shift start time
                                  Ivan Ivanov

                                  Hi Ossai,

                                   

                                  I was getting the same error all day yesterday - only different numbers after dimension____

                                   

                                  However, I managed to achieve a solution which I think is correct and have sent it to our controlling department which will provide me with feedback soon.

                                   

                                  I have changed the following under Billing date:

                                   

                                  DATE(

                                  IF ISNULL([Start 2]) AND NOT ISNULL([End 2]) THEN DATEADD('day', -1, DATETRUNC('day',[Detect date]))

                                  ELSEIF

                                  NOT ISNULL([Start 2]) AND NOT ISNULL([End 2]) AND [Start 2] > [Detect date]

                                        THEN DATEADD('day', -1, DATETRUNC('day',[Detect date]))

                                  ELSEIF

                                  NOT ISNULL([Start 2]) AND NOT ISNULL([End 2]) AND [Start 2] = [End 2]

                                        THEN DATEADD('day', -1, DATETRUNC('day',[Detect date]))

                                  ELSE DATETRUNC('day', [Detect date]) END

                                  )

                                   

                                  With the part ELSEIF NOT ISNULL([Start 2]) AND NOT ISNULL([End 2]) AND [Start 2] = [End 2] I presume that if there are CLOCK-IN and OUT entries at the same time, the correct one is the out (I'll see how this behaves when the correct is IN ). This is catching a particular case that we have. I am waiting to see whether it gives errors for other workers. Also, I think that if noone clocks in and out at the same time, this particular ELSEIF statement is not needed.

                                   

                                  I will post if this works fine or if I need or have made any corrections.

                                   

                                  Cheers, Ossai!

                                   

                                  Ivan

                                  1 2 Previous Next