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.
B to G are all that available. H is as far as i can calculate, I and J are added for explanation purposes..
Your last entry has started on 19:32 whereas 19:30 to 20:15 is break time but you didn't count this break. You counted only 00:00 to 01:00 only. Should this 43 minutes of break between 19:32 to 20:15 be excluded so that total break should 1 hour 43 minutes for last entry?