Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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 ?
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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

5 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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