Splitting Employee Punches by Factory Shift

[MStark]

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 works? Or would a different formula work better? Any assistance will be appreciated!

Example -
Second Punch In =9/21/2023 11:03:00 PM
Second Punch Out =9/22/2023 3:31:00 PM

formula=if [Second Punch In] = null then 0
else if Time.From([Second Punch Out]) > Time.From([Second Punch In]) then
List.Max(
{0, List.Min({15, 24 * Number.From(Time.From([Second Punch Out]))})
- List.Max({7, 24 * Number.From(Time.From([Second Punch In]))})})
else
List.Max({0, 15 - 24 * Number.From(Time.From([Second Punch In]))})
+ List.Max({0, 24 * Number.From(Time.From([Second Punch Out])) - 7})

Thanks in advanced for your time and effort!

[MStark]

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]

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]

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:35

12/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]

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]

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)

[spinfuzer]

Try this one.  Did not account for start of shifts in the prior day.

let
    punch_in = [First Punch In], // update to punch in column
    punch_out = [First Punch Out], //update to punch out column
    shifts = {#duration(0,7,0,0), #duration(0,15,0,0), #duration(0,23,0,0), 
        #duration(-1,7,0,0), #duration(-1,15,0,0), #duration(-1,23,0,0)},
    start_of_shift = Date.From(punch_in) & #time(0,0,0) + 
        List.Select(
            shifts, 
            (x) => 
            [ 
                a = Duration.Hours(punch_in-(Date.From(punch_in) & #time(0,0,0) + 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 
    try Table.FromRecords(generate_shifts) otherwise null

[jennratten]

@MStark @The solution I provided is based on the punch in time, but should identifying the shift be based on any other critieria

[spinfuzer]

As for how to adjust your existing formula, I think you have to limit the number of hours to 8 for the shift.

So a List.Max({ 0, List.Min({your existing formula,8}) } ) for example.

[spinfuzer]

solution using list.generate to generate the shifts.  

Add Custom Column with this formula and then expand the column.

let
    punch_in = [Second Punch In], // update to punch in column
    punch_out = [Second 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))

[MStark]

Thanks @spinfuzer!! Sorry I wasnt clear in what Im looking for. See reply to my original post. Let me know if you  can help

[Anonymous]

Hi @MStark 

Base on your describtion, I make the following solution, I split the time to the related shift and calculated the time between the related shift.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtQ3MjAyVjA0tDIwtjIwUAjwVdIBiRtBxI2tjA3hwolKsTrImoyx6zGzMgLrcQQJJynFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Second Punch In" = _t, #"Second Punch Out" = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Second Punch In", type datetime}, {"Second Punch Out", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let _hours=Duration.Hours([Second Punch Out]-[Second Punch In]),
_minutes=Duration.Minutes([Second Punch Out]-[Second Punch In]),
_totalhours=Number.RoundUp((_hours*60+_minutes)/(60*8))
in List.Numbers(1,_totalhours,1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each #duration(0,([Custom]-1)*8,0,0)+[Second Punch In]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Custom.1]+#duration(0,8,0,0)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PunchIn", each let _time0=Date.From([Custom.1])&#time(12,0,0),
_time1=Date.From([Custom.1])&#time(7,0,0),
_time2=Date.From([Custom.1])&#time(15,0,0),
_time3=Date.From([Custom.1])&#time(23,0,0)
in if [Custom]=1 then [Second Punch In] else  if  [Custom.1]>_time0 and [Custom.1]>=_time3 then _time3 else if [Custom.1]>_time0 and [Custom.1]<_time3 and [Custom.1]>=_time2 then _time2 else _time1),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "PunchOut", each let _time0=Date.From([Custom.2])&#time(12,0,0),
_time1=Date.From([Custom.2])&#time(7,0,0),
_time2=Date.From([Custom.2])&#time(15,0,0),
_time3=Date.From([Custom.2])&#time(23,0,0),
_maxindex=List.Max(Table.SelectRows(#"Added Custom3",(x)=>x[Type]=[Type])[Custom])
in if [Custom]= _maxindex then [Second Punch Out] else if  [Custom.2]>_time0 and [Custom.2]>=_time3 then _time3 else if [Custom.2]>_time0 and [Custom.2]<_time3 and [Custom.2]>=_time2 then _time2 else _time1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Second Punch In", "Second Punch Out", "Custom", "Custom.1", "Custom.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"PunchIn", type datetime}, {"PunchOut", type datetime}}),
    #"Added Custom5" = Table.AddColumn(#"Changed Type1", "Hours", each Number.RoundDown((Duration.Hours([PunchOut]-[PunchIn])*60+Duration.Minutes([PunchOut]-[PunchIn]))/60,1)),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "Judge", each if [PunchIn]>=Date.From([PunchIn])&#time(23,0,0) then "11-7" else if [PunchIn]>=Date.From([PunchIn])&#time(7,0,0) and [PunchIn]<Date.From([PunchIn])&#time(15,0,0) then "7-3" else "3-11")
in
    #"Added Custom6"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MStark]

Thanks @Anonymous! Seems like I wasnt clear in what I wanted. See reply to my original post

[jennratten]

Hello!  Please give this a try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtQ3MjAyVjA0tDIwtjIwUAjwVdIBiRtBxI2tjA2hwrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Second Punch In" = _t, #"Second Punch Out" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Second Punch In", type datetime}, {"Second Punch Out", type datetime}}),
    #"Inserted Hour" = Table.AddColumn(#"Changed Type", "Shift", each let StartHour = Time.Hour([Second Punch In]) in if StartHour >= 23 then "11-7" else if StartHour >= 15 then "3-11" else "7-3", type text)
in
    #"Inserted Hour"

[MStark]

Thanks @jennratten! This is helpful but wont work for me as Im looking for the hours to be split by shift. Have 3 columns for the different shifts and want the hours split between the 3 columns. In the sample you gave, would want 8 hours to be in the 11-7 column, 8 in 7-3 and .5 in 3-11

Any ideas?