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
jennratten's avatar
jennratten
Super User
2 years ago

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's avatar
    MStark
    Helper III
    2 years ago

    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?