Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AZJohnPowerBI
Helper I
Helper I

Converting Time Text to Numbers

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.

AZJohnPowerBI_0-1665587679621.png

 



Any help would be greatly appreciated.

1 REPLY 1
v-yueyunzh-msft
Community Support
Community Support

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.