Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to convert time (HH:MM:SS) into integer?

 

Hello all,

 

I am new with Power BI, but i am already excited about the possiblities.  I would like to know how to convert a time (HH:MM:SS) into a integer (12345)?

 

 

Thanks !

 

 

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    From the table structure provided above, I see you also have a "STARTDATE" and a"ENDATE" column, so the STARTTIME and ENDTIME could in different dates, right?

     

    In this scenario, before using the DATEDIFF funtion, you may need to combine the start/end Date and start/end Time to start/end DateTime first. See my sample below.:smileyhappy:

     

    Assume we have a table called "Table1" like below.

     

     

    1. Use the formula below to create a calculate column called "STARTDATETIME" to combine the STARTDATE column and STARTTIME column.

    STARTDATETIME = Table1[STARTDATE] + Table1[STARTTIME]

    2. Use the formula below to create a calculate column called "ENDDATETIME" to combine the ENDDATE column and ENDTIME column.

    ENDDATETIME = Table1[ENDDATE] + Table1[ENDTIME]

    3. Then you should be able to use the formula below to calculate the duration in minutes.

    DURATION = DATEDIFF(Table1[STARTDATETIME], Table1[ENDDATETIME], MINUTE)

     

    Regards

16 Replies

  • MrPowerBIPro's avatar
    MrPowerBIPro
    Advocate II
    9 years ago

    It's simple my friend, use this formula:

     

    ConvertedTimeToInteger is calculated column!

    use Hour,Minute,Second functions :smileywink:

     

    ConvertedTimeToInteger = HOUR(Table1[Time])*3600+MINUTE(Table1[Time]*60)+SECOND(Table1[Time])

    see the picture.

    I am ready for any help! 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hello,

       

      The requirement of the duration time (in minutes) for product A is ENDTIME- STARTTIME. So i will give you an example.

       

      Product A started this morning (STARTTIME) at 07:15 and ended at 07:44 (ENDTIME). I want to have a new calculated column that can return the integer value of 29 minutes(ENDTIME - STARTTIME).

       

      Which formula should i use my friend?

       

       

      Greets,

       

      Rega

       

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    or if u want to convert HH:MM:SS to interger as secs , Try this 

     

    create calculated column = var time = time column

    var hours = left(time,2) * 3600

    var Mins= mid(time,4,2) * 60

    var Secs= right(time,2) 

     

    return  hours + Mins + Secs

     

    try and let me know if any help 

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hello Baskar,

       

      I want to convert HH-MM-SS into a integer as minute.

       

      Do i have create a new measure or a new column (based on the following query) ?

      create calculated column = var time = time column

      var hours = left(time,2) * 3600

      var Mins= mid(time,4,2) * 60

      var Secs= right(time,2) 

       

      return  hours + Mins + Secs

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    Try this :

     

    Test (HH:MM:SS) := VAR Duration = sum(Duration)  -- Replace this with your column
    VAR Hours =
    ROUNDDOWN ( Duration /3600 , 0 )
    VAR Minutes =
    ROUNDDOWN ( ( Duration - ( Hours *3600 ) )/60, 0 )
    VAR Seconds =
    ROUND(MOD ( Duration - ( Hours *3600 ),60 ),0)
    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 (
    H,
    CONCATENATE ( "h : ", CONCATENATE ( M, CONCATENATE ( "m : ", CONCATENATE(S,"s") ) ) )