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

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