Cannot Convert value 'xx:xx:xx' of type Text to type Integer

[Anonymous]

Hi All,

I am new to Power Bi and this is a problem im having:

I want to calculate the duration between two dates columns:

First Book On	Last Book Off
01/01/2020 20:20	02/01/2020 00:15
04/01/2020 20:06	04/01/2020 22:36
05/01/2020 19:59	05/01/2020 23:08
06/01/2020 23:20	07/01/2020 00:11
07/01/2020 21:17	07/01/2020 23:10
09/01/2020 17:53	09/01/2020 23:57

I used the following code to calculate the duration below:

Time =

VAR Duration = DATEDIFF([First Book On], [Last Book Off], SECOND )

VAR Hours =

INT ( Duration / 3600)

VAR Minutes =

INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

VAR Seconds =

ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0)

VAR H =

IF ( LEN ( Hours ) = 1,

CONCATENATE ( "0", Hours ),

CONCATENATE ( "", Hours )

)

VAR M =

IF (

LEN ( Minutes ) = 1,

CONCATENATE ( "0", Minutes ),

CONCATENATE ( "", Minutes )

)

VAR S =

IF (

LEN ( Seconds ) = 1,

CONCATENATE ( "0", Seconds ),

CONCATENATE ( "", Seconds )

)

RETURN

CONCATENATE (

H,

CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )

)

After running the code, I get #ERROR in Time column and received an error message: 

Cannot Convert value 'hh:mm:ss' of type Text to type Integer.

How can I solve this?

[camargos88]

Hi @Anonymous ,

Try this column on Power Query:

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Greg_Deckler]

See if these help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92

Did you set your Time column to a data type of integer? You would need it set to a type of text. Otherwise, see the first article to see how you can leave it in a certain integer format and display it as a duration.

[Anonymous]

"Did you set your Time column to a data type of integer? You would need it set to a type of text. Otherwise, see the first article to see how you can leave it in a certain integer format and display it as a duration."

Yes I set the time column data type to Text but I get negative numbers on some rows:

First Book On	Last Book Off	Duration
01/01/2020 20:20	02/01/2020 00:15	-21:55:00
04/01/2020 20:06	04/01/2020 22:36	02:30:00
05/01/2020 19:59	05/01/2020 23:08	03:09:00
06/01/2020 23:20	07/01/2020 00:11	-20:32:00
07/01/2020 21:17	07/01/2020 23:10	02:36:00
09/01/2020 17:53	09/01/2020 23:57	03:19:00