# DATETIME Difference calculation

#### kranthi157

##### New Member
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)

#### Attachments

• 11.7 KB Views: 7

#### AlanSidman

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

Code:
``````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"``````

#### Attachments

• 30.9 KB Views: 6

#### kranthi157

##### New Member
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

##### Active Member
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

##### Well-Known Member
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

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

Cheers