how to get hours and minutes between dates

[Anonymous]

Hi,

I have two fields start Date_time and End Date_Time, i want to calculate hours and mintures spend between two dates, kindly advise how to calculate total time in hours and minutes.

 Start Time End Time WorkingHours
11/6/2017 9:30      11/6/2017 11:30 0
11/6/2017 12:00   11/6/2017 13:30 0
11/6/2017 14:30   11/6/2017 17:30 0
11/6/2017 17:31   11/6/2017 18:00 0
11/7/2017 9:00     11/7/2017 10:00 0
11/6/2017 9:00     11/6/2017 18:00 0
11/6/2017 22:00 11/7/2017 7:00 0
11/7/2017 22:00 11/8/2017 1:30 0
11/6/2017 9:30 11/7/2017 13:00

[Anonymous · Accepted solution]

@Anonymous,

Perhaps this post can help you: https://community.powerbi.com/t5/Desktop/Calculating-day-time-difference/m-p/321189

[Anonymous]

Also can use a measure, i feel little faster loading time with this 

TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)

and then change the format to time hh:mm:ss

Cheers

[Anonymous]

Hi

The below formula will get you the hour difference between the two datetime value. By changing the last parameter to HOUR/ MINUTE/SECOND , you can get the desired result.

HrDiff = DATEDIFF(Table1[StartTime],Table1[EndTime],HOUR)

HrDiff = DATEDIFF(Table1[StartTime],Table1[EndTime],MINUTE)

Thanks
Raj

[Anonymous · Accepted solution]

@Anonymous,

Perhaps this post can help you: https://community.powerbi.com/t5/Desktop/Calculating-day-time-difference/m-p/321189

[Omosanya93]

Hello Everybody,

Please I need your comment and advice on this data.

Have been having issues with this data. The data is for the month of September 2021. But when I upload it into power bi it brings a date dated 1899. I have tried all efforts to change my locale in the settings but the error persists.

OPERATING TIME	Target HOURS	Days	DATE	OPENING TIME	CLOSING TIME	OPERATING TIME	OVER HOURS
6am-7pm	13:00:00	Wednesday	1-Sep	7:00	18:55	11:55	0:00
6am-7pm	13:00:00	Thursday	2-Sep	7:10	18:55	11:45	0:00
6am-7pm	13:00:00	Friday	3-Sep	7:10	18:55	11:45	0:00
0.00	0:00:00	Saturday	4-Sep	8:05	10:25	2:20	2:20
0.00	0:00:00	Sunday	5-Sep	11:40	19:05	7:25	7:25
6am-7pm	13:00:00	Monday	6-Sep	7:10	18:55	11:45	0:00
6am-7pm	13:00:00	Tuesday	7-Sep	7:10	18:55	11:45	0:00

[Anonymous · Accepted solution]

---

@Anonymous wrote:

@Anonymous,

Perhaps this post can help you: https://community.powerbi.com/t5/Desktop/Calculating-day-time-difference/m-p/321189

---

As mentioned in the above post, Duration.ToRecord([EndTime]-[StartTime]) also handles the requirement perfectly in QueryEditor, whereas DATEDIFF is DAX.

[Anonymous]

Hi,

If i use MINUTE, it give result as 30,60,180,210,119,104 and

if i use HOURS, it 30 minutes utilized it say 1, 1.5 say 2.

how is poosible to calculate hours and minutes altogether for example if 140 minutes it should say 2:10 Hrs.

Pls guide.

Thx

[Anonymous]

As mentioned in the above post, Duration.ToRecord([EndTime]-[StartTime]) also handles the requirement perfectly in QueryEditor, You will get the difference in Hr, Min, Sec in different columns, you can concatenate Hrs and Mins column to get the desired result.

Hope this helps!

Thanks

Raj