Forum Discussion
Substract break time and shift change from durations
- 4 years ago
1. Since 4th entry finished at 07:34 and break started at 07:30, hence total break time will be = 43+60+4 = 107 minutes = 1.78 hours
Also total minutes for this entry = start of 07:32 to end of 07:34 = 12*60+2 = 722 minutes = 12.03 hours
Hence net duration = 12.03-1.778 = 10.25 hours
2. For second entry = Total duration is 309 minutes which is 5.15 hours not 5.2 hours
Below are correct results
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bYxJCsAgEAS/Ip4Fe2YUcW55gJC7+P9vBHGJgVyruqtWGzxnz2A2UQkKmKtYd2AijaHzu9jmzkNWwRT9IBiYVfLsjP0SpEg/+6Qkn/4rhPc+eto0rHx7AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start time" = _t, #"Finish time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start time", type datetime}, {"Finish time", type datetime}}), ListOfBreaksInMinutes = List.Times(#time(0,0,0),60,#duration(0,0,1,0))&List.Times(#time(7,30,0),45,#duration(0,0,1,0))&List.Times(#time(12,0,0),60,#duration(0,0,1,0))&List.Times(#time(19,30,0),45,#duration(0,0,1,0)), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Round(List.Count(List.Difference(List.Transform(List.DateTimes([Start time],Duration.TotalMinutes([Finish time]-[Start time]),#duration(0,0,1,0)),each Time.From(_)),ListOfBreaksInMinutes))/60,2)) in #"Added Custom" - 4 years ago
Better to create 2 columns Start_Time and Finish_Time were you should set seconds to 0. Then you should refer to these 2 fields in the formula. In the end, delete these 2 columns.
Better to create 2 columns Start_Time and Finish_Time were you should set seconds to 0. Then you should refer to these 2 fields in the formula. In the end, delete these 2 columns.
Got it, thanks a lot!