Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Convert number to time format

I am trying to convert a column to time format. The minutes in the column REPLY_TIME_IN_CALENDAR_MINUTES are in data type Decimal Number. 

The number I trying to convert is 4805 which should be 80h:5m:0s or 3d:8h:5m but I am getting everything correct except the days. 

 

See image 

Need Help
Show and Tell
Tips and Tricks
  • ERD's avatar
    ERD
    5 years ago

    Hi Anonymous ,

    Please, find the options below:

    In case of calculated columns:

    d:h:m = 
VAR currentNum = 'T'[Minutes]
RETURN
INT(currentNum/1440) & ":" &                         
RIGHT("0" & INT(MOD(currentNum/60,24)),2) & ":" &    
RIGHT("0" & INT(MOD(currentNum/1,60)),2) 
    hh:mm:ss = 
VAR currentNum = 'T'[Minutes]
RETURN
RIGHT("0" & INT (currentNum/60),2) & ":" &           
RIGHT("0" & INT(MOD(currentNum,60)),2) & ":" &          
RIGHT("0" & INT(MOD(currentNum,1)),2)

    If you need a measure, then replace 

    var currentNum = 'T'[Minutes]
    with 
    var currentNum = SELECTEDVALUE('T'[Minutes])
     
    Did I answer your question? Mark my post as a solution!

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try like

     

    time([REPLY_TIME_IN_CALENDAR_MINUTES]/60, trunc(mod([REPLY_TIME_IN_CALENDAR_MINUTES],60),0), ([REPLY_TIME_IN_CALENDAR_MINUTES] -trunc([REPLY_TIME_IN_CALENDAR_MINUTES],0))*100/60)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak Does not give me the what I need.. 

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Anonymous , not able to get an issue with screenshot.

        Can you share sample data and sample output in table format?

         

  • ERD's avatar
    ERD
    Community Champion
    5 years ago

    Hi Anonymous ,

    Please, find the options below:

    In case of calculated columns:

    d:h:m = 
VAR currentNum = 'T'[Minutes]
RETURN
INT(currentNum/1440) & ":" &                         
RIGHT("0" & INT(MOD(currentNum/60,24)),2) & ":" &    
RIGHT("0" & INT(MOD(currentNum/1,60)),2) 
    hh:mm:ss = 
VAR currentNum = 'T'[Minutes]
RETURN
RIGHT("0" & INT (currentNum/60),2) & ":" &           
RIGHT("0" & INT(MOD(currentNum,60)),2) & ":" &          
RIGHT("0" & INT(MOD(currentNum,1)),2)

    If you need a measure, then replace 

    var currentNum = 'T'[Minutes]
    with 
    var currentNum = SELECTEDVALUE('T'[Minutes])
     
    Did I answer your question? Mark my post as a solution!
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Brilliant!
      Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      ERD Is there a way to get a sum or an average of that measure? Since that is text I can't get a good measure by that. 

      • ERD's avatar
        ERD
        Community Champion
        5 years ago

        Anonymous ,

        Work with minutes and then convert to the needed format.

        E.g.: var avgMinutes = AVERAGE('number to time format'[Minutes])