Forum Discussion

MStark's avatar
MStark
Helper III
2 years ago

Splitting Employee Punches by Factory Shift

Im trying to split my employee punches by shift time (7-3, 3-11, 11-7) but formula below is giving me more than 8 hours for some shifts which doesnt make sense. What needs to be updated so that this ...
Need Help
MStark's avatar
MStark
Helper III
2 years ago

Thanks to all those that tried to help!!! Really appreciate all the assistance!

Seems like I wasnt clear on my original post. Im looking to split the hours into 3 columns for the 3 shifts. 

 

 

The code in original post is working for most of the times but think it gets confused when the shift goes overnight.

 

Anyone have any good ideas?

 

Thanks in advance for your assistance!!

spinfuzer's avatar
spinfuzer
Solution Sage
2 years ago

If the night is your only issue the below should be OK I think.

 

check if the punch out time is after 11PM punch in day.  Then take total duration between min of (punch out time,11 AM next day) and max of (punch in time,11pm same day).

 

 

if [Second Punch In] = null then 0
else if [Second Punch Out] >= Date.From([Second Punch In]) & #time(23,0,0)
then Duration.TotalMinutes(
    List.Min(
        {
            [Second Punch Out],
            Date.From([Second Punch In]) & #time(0,0,0) + #duration(1,7,0,0)
        }
    ) - 
    List.Max(
        {
            [Second Punch In],
            Date.From([Second Punch In]) & #time(23,0,0)
        }
        )
    )/60
else 0

 

 

 

 

 

 

 

 

  • MStark's avatar
    MStark
    Helper III
    2 years ago

    Thank you spinfuzer! This works for the 11p-7a shift but not generating correctly for all punches. For example getting 0 for these punches when theres around 1.5 hours that fall in the 11p-7a shift
    First Punch InFirst Punch Out

    12/1/2022 5:3512/1/2022 14:28




    Im also having an issue with the code Im using for the 3p-11p shift since some punches are generating more than 8 hours.

     

    For Example - 

     

    Code Im using to generate the 3-11 1 column is:

    if Time.From([First Punch Out]) > Time.From([First Punch In])
    then List.Max(
    {0,List.Min({23, 24*Number.From(Time.From([First Punch Out]))})
    -List.Max({15, 24*Number.From(Time.From([First Punch In]))})})
    else List.Max({0, 23 - 24*Number.From(Time.From([First Punch In]))})
    +List.Max({0, 24*Number.From(Time.From([First Punch Out]))-15})

     

    What am I doing wrong here since its not possible to have more than 8 hours between 3p-11p (unless the punch times are over 24 hours which none are). Any way you can help?

     

    Thanks in advance!

    • spinfuzer's avatar
      spinfuzer
      Solution Sage
      2 years ago

      Check this one for the 2nd shift.  I still believe that doing it the original way i posted, or anyone elses that breaks up the shifts into 8 hours increments and then pivoting the results is the best approach.  What if you have someone who works so long that they are in shift 2 prior day and end in shift 2 next day for some reason?  Forcing the times into 3 columns implies that we are never going to have employees that work into the same shift in the next day(s).

       

      Let me think about the other shift formula.  It is probably a similar approach to below.

       

       

      if [First Punch In] = null then 0
else 
    List.Max({Duration.TotalMinutes(
        if [First Punch Out] >= Date.From([First Punch In]) & #time(23,0,0) // end after start of shift
        then
            List.Min(
                {
                    [First Punch Out],
                    Date.From([First Punch In]) & #time(0,0,0) + #duration(1,7,0,0) // next day end of shift
                }
            ) - 
            List.Max(
                {
                    [First Punch In],
                    Date.From([First Punch In]) & #time(23,0,0)
                }
            )
        else //if [First Punch In] <= Date.From([First Punch In]) & #time(7,0,0) then Duration.TotalMinutes(
            List.Min(
                {
                    [First Punch Out],
                    Date.From([First Punch In]) & #time(7,0,0) // same day end of shift
                }
            ) - 
            List.Max(
                {
                    [First Punch In],
                    Date.From([First Punch In]) & #time(0,0,0) - #duration(1,0,0,0) // prior start of shift
                }
            )
        )/60
    ,0
    })

       

       

         

      • MStark's avatar
        MStark
        Helper III
        2 years ago

        Appreciate all your help spinfuzer! Trying the code in your original response but getting the following error

        Expression.Error: There weren't enough elements in the enumeration to complete the operation.
        Details:
        [List]

         

         

        let
        punch_in = [First Punch In], // update to punch in column
        punch_out = [First Punch Out], //update to punch out column
        shifts = {#time(7,0,0), #time(15,0,0), #time(23,0,0)},
        start_of_shift = Date.From(punch_in) &
        List.Select(
        shifts,
        (x) => [
        a = Duration.Hours(punch_in-(Date.From(punch_in) & x)),
        b = a < 8 and a >= 0
        ][b]
        ){0},
        generate_shifts = List.Generate( () =>
        [start = start_of_shift, end = start + #duration(0,8,0,0)],
        (x) => punch_out > x[start],
        (x) => [start = x[start] + #duration(0,8,0,0), end = start + #duration(0,8,0,0)],
        (x) => [
        shift_start = x[start],
        shift_end = x[end],
        hrs = Duration.TotalMinutes(
        List.Min({punch_out,shift_end})-
        List.Max({punch_in,shift_start})
        )/60,
        shift = Text.From(Number.Mod(Time.Hour(shift_start),12)) & " - " & Text.From(Number.Mod(Time.Hour(shift_end),12))
        ]
        )
        in
        Table.FromRecords(generate_shifts)