Substract break time and shift change from durations

[Anonymous]

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!

[Vijay_A_Verma · Accepted solution]

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"

[Anonymous]

You right, below is the revised table.

[Vijay_A_Verma · Accepted solution]

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"

[Anonymous]

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))

[Vijay_A_Verma · Accepted solution]

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.

[Anonymous]

Got it, thanks a lot!

[Vijay_A_Verma]

Are columns H, I, J given or they also need to be calculated?

[Anonymous]

B to G are all that available. H is as far as i can calculate, I and J are added for explanation purposes..

[Vijay_A_Verma]

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?