How to convert a Text data type to Time Data type ( HH:MM) ?

[Anonymous]

Hi Team,

Quick help needed please

I have created a calculated column based on a column which is in time duration ( Seconds) . 

Column Name : Time Duration ( Seconds) 

Sample Data :     Time Durations(Seconds) 

                               390920

                               29822

                               2882

                               282829

All those are 5 different records and those are in seconds .  I have used a formulae and calculated a column to convert this seconds into Hours minutes seconds format ,i.e (HH:mm:ss) . 

See the formulae below : 

HHMMSS = FORMAT(TIME(int('Major Incident'[Time to Resolve-Major]/3600), int(mod('Major Incident'[Time to Resolve-Major],3600)/60),int(mod(mod('Major Incident'[Time to Resolve-Major], 3600)/60))))

So Everything worked well till here . The Calculated column(HHMMSS) shown above  is in text format and doesnt allow us to change it to required format Time (HH:MM) . 

Could some one help us to achieve this ?

[v-cherch-msft]

Hi @Anonymous 

You may create the column as below and then change the format.

Time = 
VAR a = 'Major Incident'[ Time Durations(Seconds) ]
VAR hours =
    INT ( a / 3600 )
VAR minutes =
    INT ( MOD ( a - ( hours * 3600 ), 3600 ) / 60 )
VAR seconds =
    ROUNDUP ( MOD ( MOD ( a - ( hours * 3600 ), 3600 ), 60 ), 0 )
RETURN
    TIME ( hours, minutes, seconds )

Regards,

Cherie

[Anonymous]

Hi Cherie,

Thanks for your reply. I have created a column and updated my report . But, I still see some issue . Please check the attached screenshot. 

Time to Resolve-Major is a column which has data in Seconds. So if we take first row, it has 626220 seconds .

I manually divided it which should be 626220/3600 = 173.95 hours . But I am getting 05:57 (hours mins) using your formulae .

Note: Time is the column which holds the data created using your formuale.   Am I doing some thing wrong ? Could you please assist ?

[v-cherch-msft]

Hi @Anonymous 

If you want to change the value to Time format. The value should be in 24 hours.If the hours are >24,it could not be changed to time format.So my formula is calculated in 24 hours.

Regards,

Cherie

[Anonymous]

Hi Cherie,

Thanks for your reply. I have understood the 24 hour format . I have one more thing in addition to this. 

Now that we have got time duration in HH:MM format for all records in table . I want to take an average out of it and show that in a Card thats available in Power BI Visuals. 

It is either showing First or Last Duration , but not average. Can you help me with this ?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try below measure:

Measure =
FORMAT ( AVERAGE ( 'Major Incident'[Time] ), "HH:MM" )

If it is not your case,I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.

Regards,

Cherie