Duration format change

[ursua]

Hi,
So i have a column [Custom] which is a result of subtraction between a column with start date and end date in format like that 1,2345 etc. i want to change it to duration but only in format HH:MM:SS, not DD.HH:MM:SS. I tried to add a new column using Custom Column function like that: = FORMAT(Table1[Custom],"hh:mm:ss"), but it didn't work (Power BI says that it can't recognize 'FORMAT'.
Any ideas how to get desired format?

 

[Jeffrey Mahoney]

Well, your going to have to do the math to calculate the difference
=HOUR(H31-G31)+((DAYS(H31,G31)-1)*24)&":"&MINUTE(H31-G31)&":"&SECOND(H31-G31)
Where H31 is the end date/time and G31 is the beginning date/time

 

[ursua]

Hi,
Unfortunately doesn't work for me because "The expression "HOUR" wasn't recognized."

 

[Jeffrey Mahoney]

The function HOUR has been around for years

 

[Fluff]

@Jeffrey Mahoney
Not sure if you realised, but this is in the Power BI forum.

 

[sandy666]

is that what you want?

SDT	EDT	Duration	DurationTime
20/05/2020 10:55	22/05/2020 09:10	1.22:14:30	46:14:30

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"SDT", type datetime}, {"EDT", type datetime}}),
    Duration = Table.AddColumn(Type, "Duration", each [EDT] - [SDT], type duration),
    DurationTime = Table.AddColumn(Duration, "DurationTime", each Text.From(Duration.Days([Duration]) * 24 + Duration.Hours([Duration]))&":"&Text.From(Duration.Minutes([Duration]))&":"&Text.From(Duration.Seconds([Duration])))
in
    DurationTime

 

[sandy666]

or even like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"SDT", type datetime}, {"EDT", type datetime}}),
    DurationTime = Table.AddColumn(Type, "DurationTime", each Text.From(Duration.Days([EDT] - [SDT]) * 24 + Duration.Hours([EDT] - [SDT]))&":"&Text.From(Duration.Minutes([EDT] - [SDT]))&":"&Text.From(Duration.Seconds([EDT] - [SDT])))
in
    DurationTime

 

[sandy666]

or in case time is eg. 05:02:07

SDT	EDT	DurationTime
20/05/2020 10:55	22/05/2020 09:10	46:14:30
20/05/2020 00:01	22/05/2020 01:03	49:01:30
20/05/2020 05:07	22/05/2020 08:05	50:58:00
20/05/2020 00:01	22/05/2020 00:00	47:59:00

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"SDT", type datetime}, {"EDT", type datetime}}),
    DurationTime = Table.AddColumn(Type, "DurationTime", each Text.PadStart(Text.From(Duration.Days([EDT] - [SDT]) * 24 + Duration.Hours([EDT] - [SDT])), 2, "0")&":"&Text.PadStart(Text.From(Duration.Minutes([EDT] - [SDT])), 2, "0")&":"&Text.PadStart(Text.From(Duration.Seconds([EDT] - [SDT])), 2, "0"))
in
    DurationTime