Forum Discussion
How to convert time (HH:MM:SS) into integer?
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
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!
Anonymous
See this again:
https://msdn.microsoft.com/en-us/library/dn802538.aspx
Term Definition
| start_date | A scalar datetime value. |
| end_date | A scalar datetime value Return Value. |
| interval | The 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?
Hi MrPowerBIPro,
I tried to follow your advize but then i still have a error message by entering the formula. check the image below.
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
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 :)