Need help with formula to calculate hourly working mins(or hours) using clock-in/out timestamps

[Anonymous]

Hi All,

I am trying to fill empty cells using formula. I have clock-in, clock-out timestamps, and total hours. I then have 24 (24 hrs) columns header, each column is an hour (12am, 1am,.....10am, 11am,...7pm, 8pm....11pm). Now I have to fill cells of these columns with work hours.

I couldn't find an option to attach my file while posting this message, I have provided screenshot of my spreadsheet though to explain my problem. I have to create formula for colored cells and rest of the information is available.

I'd highly appreciate any related assistance.

Thanks,

Shivam

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Form your description, I could not figure out how to calculate in M2(0.07,highlight with yellow), also you could refer to below code with query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrMyNbMyMFBw9FXSUTJH5pjqmRoBKQSK1YlWMjSyMrUEKQkAK7EyNoFzTPTM0NTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Clock-in" = _t, #"Clock-out" = _t, #"Total hrs" = _t, #"5:00:00 AM" = _t, #"6:00:00 AM" = _t, #"7:00:00 AM" = _t, #"8:00:00 AM" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Clock-in", type time}, {"Clock-out", type time}, {"Total hrs", type number}, {"5:00:00 AM", type text}, {"6:00:00 AM", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Clock-in", "Clock-out", "Total hrs"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Attribute", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each ([#"Clock-in"]-[Attribute])*1440),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom.1", each if
Number.Abs([Custom])>60 then 0 else 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Attribute", type text}}, "en-AS"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Attribute", type text}}, "en-AS")[Attribute]), "Attribute", "Custom.1", List.Sum)
in
    #"Pivoted Column"

You could click the right bar to see each step:

Also you could download the pbix file to have a view.

Regards,

Daniel He