Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 !

 

 

 

2016-12-27 14_48_40-Orakel Dashboard Performance - Power BI Desktop.png

 

1 ACCEPTED SOLUTION

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.Smiley Happy

 

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

 

t1.PNG

 

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)

c3.PNG

 

Regards

View solution in original post

16 REPLIES 16
MrPowerBIPro
Helper II
Helper II

It's simple my friend, use this formula:

 

ConvertedTimeToInteger is calculated column!

use Hour,Minute,Second functions Smiley Wink

 

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

see the picture.

I am ready for any help! 

 

help1.PNG

Enemy of can't
www.mrpowerbi.pro
Anonymous
Not applicable

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

 

Heelo,

did you try this function?

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

Let me know if you have any problem.

Enemy of can't
www.mrpowerbi.pro

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
Not applicable

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!

 

2016-12-28 15_43_10-Orakel Dashboard Performance_NEW - Query Editor.png

@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

 

help1.PNG

 Is it ok?

 

 

 

Enemy of can't
www.mrpowerbi.pro
Anonymous
Not applicable

Hi @MrPowerBIPro,

 

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

 

2016-12-28 16_26_27-Orakel Dashboard Performance_NEW - Power BI Desktop.png

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.Smiley Happy

 

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

 

t1.PNG

 

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)

c3.PNG

 

Regards

This error happend when you have a "Start Date" greater than "End Date"! check the columns!
as in the link mentioned:

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

Remarks:
An error is returned if start_date is larger than end_date.

Finally check your data! the formula is correct my friend 🙂

Enemy of can't
www.mrpowerbi.pro
Baskar
Resident Rockstar
Resident Rockstar

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
Not applicable

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

create new column . Do u want Minute level right ? so no need sec

 

create calculated column = var time = time column

var hours = left(time,2) * 60

var Mins= mid(time,4,2) 

 

 

return  hours + Mins

 

 

this is enough, try this . let me know

Anonymous
Not applicable

Hi Baskar,

 

it doesn't work.

 

I copy pasted your formula after creating a new column. Check the error message .

 

2016-12-27 16_56_41-Orakel Dashboard Performance - Power BI Desktop.png

createcalculated

 

this is the Formula :

 

column = var time = time column -- here u have to replace your time column name 

var hours = left(time,2) * 60

var Mins= mid(time,4,2) 

 

 

return  hours + Mins

 

Now try

create new column

Baskar
Resident Rockstar
Resident Rockstar

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") ) ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.