DATETIME Difference calculation

[kranthi157]

I need help in finding the difference of DateTime of various jobs that we ran at our site. I have attached the sample datasheet for your reference.
Column 1 - Job ID ==> Job number that we ran
Column 2 - Start Time ==> Start Time of the Job
Column 3 - End Time ==> End Time of the Job
Column 4 - Diff in Minutes <== Need to calculate this. We can round-off the seconds to minutes as >30sec to 1 minute
Column 5 - Diff in Hours <== Need to calculate this. We can round-off the minutes to hours (>30 minutes to 1 hour) and seconds to minutes as (>30sec to 1 minute)

 

[AlanSidman]

Achieved using Power Query. Note that you have some End times that are earlier than the Start Times. Resulting in ###### in solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Start Time.1", "Start Time.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "End time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"End time.1", "End time.2"}),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Delimiter1", {{"Start Time.1", type text}}, "en-US"), "Start Time.1", Splitter.SplitTextByPositions({0, 4}, false), {"Start Time.1.1", "Start Time.1.2"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Split Column by Position", "Start Time.1.2", Splitter.SplitTextByRepeatedLengths(2), {"Start Time.1.2.1", "Start Time.1.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position1",{"Start Time.1.2.1", "Start Time.1.2.2", "Start Time.1.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start Date"),
    #"Split Column by Position2" = Table.SplitColumn(#"Merged Columns", "Start Time.2", Splitter.SplitTextByRepeatedLengths(2), {"Start Time.2.1", "Start Time.2.2", "Start Time.2.3"}),
    #"Merged Columns1" = Table.CombineColumns(#"Split Column by Position2",{"Start Time.2.1", "Start Time.2.2", "Start Time.2.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Start Time"),
    #"Split Column by Position3" = Table.SplitColumn(#"Merged Columns1", "End time.1", Splitter.SplitTextByPositions({0, 4}, false), {"End time.1.1", "End time.1.2"}),
    #"Split Column by Position4" = Table.SplitColumn(#"Split Column by Position3", "End time.1.2", Splitter.SplitTextByRepeatedLengths(2), {"End time.1.2.1", "End time.1.2.2"}),
    #"Merged Columns2" = Table.CombineColumns(#"Split Column by Position4",{"End time.1.2.1", "End time.1.2.2", "End time.1.1"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End Date"),
    #"Split Column by Position5" = Table.SplitColumn(#"Merged Columns2", "End time.2", Splitter.SplitTextByRepeatedLengths(2), {"End time.2.1", "End time.2.2", "End time.2.3"}),
    #"Merged Columns3" = Table.CombineColumns(#"Split Column by Position5",{"End time.2.1", "End time.2.2", "End time.2.3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"End Time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns3",{{"Start Date", type date}, {"End Date", type date}, {"Start Time", type time}, {"End Time", type time}}),
    #"Merged Columns4" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Start Date", type text}, {"Start Time", type text}}, "en-US"),{"Start Date", "Start Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Start"),
    #"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"End Date", type text}, {"End Time", type text}}, "en-US"),{"End Date", "End Time"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns5",{{"Start", type datetime}, {"Merged", type datetime}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each [Merged] - [Start], type duration),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Time Subtraction",{{"Subtraction", "D.H:M:S"}})
in
    #"Renamed Columns"

 

[kranthi157]

Hi AlanSidman,
Thanks for your time for giving me the solution.. Is it possible to use the Excel functions and achieve the results ?

 

[XOR LX]

Hi,

As a start:

=MMULT(TEXT(SUBSTITUTE(B2:C2,":",""),"0\/00\/00\ 00\:00\:00")+0,{-1;1})

Format the cell as you wish, e.g. [m], [h].

Regards

 

[John Jairo V]

Hi, to all!

Nice try @XOR LX ! Just one comment: You can avoid the \/ part just with "-" sign:

=MMULT(--TEXT(SUBSTITUTE(B2:C2,":",""),"0-00-00 00\:00\:00"),{-1;1})

Blessings!

 

[XOR LX]

Thanks @John Jairo V! I don't always remember all of the various date formats!

Cheers