Converting Time Text to Numbers

[AZJohnPowerBI]

I'm working as an Analyst at a Call Center and work with a lot of timestamps.
So several agents will have statistics like a call at 8:00 am was 00:22:40.

The method:
I have converted the times into seconds within Excel.
Cell*86,400.

Then within PowerBI, I apply the following formula for dd:hh:mm:ss

Avg Handle Time Measure = 
VAR Duration = AVERAGE('FACT Agent'[Avg Handle Time])
VAR Days =
	INT ( Duration / 86400)
VAR Hours =
    INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
    INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
    ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0) 
VAR D = 
	IF ( LEN ( Days ) = 1,
		CONCATENATE ( "0", Days ),
		CONCATENATE ( "", Days )
	  )
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 (
        D,
        CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
    ))

or if it is a mm:ss time:

Average Speed of Answer Measure = 
VAR Duration = AVERAGE('FACT CSQs'[Avg Speed of Answer in Seconds])

VAR Minutes =
    INT( Duration / 60)

VAR Seconds =
    ROUNDUP(MOD ( Duration - (Minutes / 60 ), 60), 0 )

VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (M, CONCATENATE ( ":", S ) ) 

The Problem:
However this only converts the timestamps into text and therefore only works within measures, and not actual calculated columns. I need to be able to use hh:mm:ss as additives. Currently within graphs, I can use a work-around solution as a measure, as some have suggested, but I am asking for help now to use the times as numbers so I can run further analytics.

Any help would be greatly appreciated.

[v-yueyunzh-msft]

Hi , @AZJohnPowerBI 

Based on your description, you want to process your timestamp text and want to return the number format. Right?

For handling text formatting, we recommend that you implement it in Power Query Editor, and you can try using Duration functions to manipulate timestamps.

For more information, you can refer to :

Duration functions - PowerQuery M | Microsoft Learn

For the specific format you want to work with, you can provide us with some of your sample data, as well as the output format you want, so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly