Change format of cell to calculate values

[craigo298er]

I have a sheet that I'm trying to format some columns to find the time difference of, but my data isn't coming out in a good format.

Sme cells that are coming out in format ## hh:mm:ss (such as "01 10:07:09" for day 1 at 10hr 07min 09sec), but I cannot set cell D2-C2 to calculate the differences. What is the formating that I need to put in to do this?

I have tried a custom dd hh:mm:ss or d hh:mm:ss and not working...neither is DATEVALUE()

 

[sandy666]

using Power Query you can replace space to dot then set format to Duration and calculate differences

 

[Fluff]

Sounds like those values are text & not numbers.
What does

=isnumber(D2)

return?

 

[craigo298er]

Sounds like those values are text & not numbers.
What does

=isnumber(D2)

return?

It does return as FALSE

 

[sandy666]

continue from post#2

Column1	Column2		Column1	Column2	Subtraction
01 10:07:09	02 03:11:01		1.10:07:09	2.03:11:01	0.17:03:52

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Source," ",".",Replacer.ReplaceText,{"Column1", "Column2"}),
    TypeDuration = Table.TransformColumnTypes(Replace,{{"Column1", type duration}, {"Column2", type duration}}),
    Subtract = Table.AddColumn(TypeDuration, "Subtraction", each [Column2] - [Column1], type duration)
in
    Subtract

 

[Fluff]

In that case if your values always start 01 you can use

=RIGHT(D2,8)-RIGHT(C2,8)

otherwise

=(LEFT(D2,2)+RIGHT(D2,8))-(LEFT(C2,2)+RIGHT(C2,8))

 

[craigo298er]

Thank you everyone, that helped a lot

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sandy666]

You are welcome