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

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi MrPowerBIPro,

 

I think datediff is calculating duration between 2 dates, if you could try some functions like Time.Minute or Time.Second

please refer: time function - msdn. Do you mean integer value is second value or something?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI tringuyenminh92,

 

Look at the following image . In the column Duration i would like to have a integer as a result. Check the row where the duration time set on 00:15:52.  In this column it must be 15 minutes. Which formula should i use then?

 

Thank you!

 

  • MrPowerBIPro's avatar
    MrPowerBIPro
    Advocate II
    9 years ago

    Anonymous

    tringuyenminh92

     

     

    See this again:

     

    https://msdn.microsoft.com/en-us/library/dn802538.aspx

     

     

    Term Definition

    start_dateA scalar datetime value.
    end_dateA scalar datetime value Return Value.
    intervalThe interval to use when comparing dates. The value can be one of the following:

    - SECOND
    - MINUTE
    - HOUR
    - DAY
    - WEEK
    - MONTH
    - QUARTER
    - YEAR

     

     Is it ok?

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi MrPowerBIPro,

       

      I tried to follow your advize but then i still have a error message by entering the formula. check the image below.

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        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