Forum Discussion
Substract break time and shift change from durations
Hi all, I have below table in which i wish to calculate column K.
As the column name suggests, it is nett duration of column H after all shift change and break time subtracted from it (shift change are at 07:30 to 08:15 and 19:30 to 20:15, and break time are from 12:00 to 13:00 and 00:00 to 01:00, all applicable to every day).
Highly appreciate any suggestions!
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"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.
8 Replies
Are columns H, I, J given or they also need to be calculated?
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?
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"That works wonder! However, i missed out to mention earlier that datetime on my data are down to seconds increment, whereas on your query it is rounded to the nearest minute, i modify your query to below. See the List.DateTimes part where i restate the [Start time] to zero the seconds, I am not sure but it seems inefficient and I am afraid it will take the query longer when refreshed. Any suggestion to simplify it?
Custom1 = Table.AddColumn(#"Reordered Columns", "Nett operation lead time", each Number.Round(List.Count(List.Difference(List.Transform(List.DateTimes(#datetime(Date.Year([Start time]),Date.Month([Start time]),Date.Day([Start time]),Time.Hour([Start time]),Time.Minute([Start time]),0),Duration.TotalMinutes([Finish time]-[Start time]),#duration(0,0,1,0)),each Time.From(_)),ListOfBreaksInMinutes))/60,1))