Forum Discussion
Difference in Hours Calculation for Duration - Negative +1 day
I have the below data, and I am trying to edit it in Power Query Editor. I wanted it to show the number of minutes between two hours - with column name - "In" and "Out" as shown in the picture. I tried it out and some of them popped as negative hours.
Since the data has only one date column and not the next day, I am not able to find out a solution.
The data has to show me 13 minutes but shows -1367 minutes
You can add a custom column with the formula below. It checks if the Out is less than the In and, if so, it sums the time between Out and midnight and In and midnight. This is in minutes so you can sub with Duration.TotalHours or just divide the result by 60.
= if [In] <= [Out] then Duration.TotalMinutes([Out] - [In]) else Duration.TotalMinutes(#time(24,0,0) - [In]) + Duration.TotalMinutes([Out] - #time(0,0,0))
Regards,
Pat
3 Replies
You can add a custom column with the formula below. It checks if the Out is less than the In and, if so, it sums the time between Out and midnight and In and midnight. This is in minutes so you can sub with Duration.TotalHours or just divide the result by 60.
= if [In] <= [Out] then Duration.TotalMinutes([Out] - [In]) else Duration.TotalMinutes(#time(24,0,0) - [In]) + Duration.TotalMinutes([Out] - #time(0,0,0))
Regards,
Pat
mahoneypat- That works great
Please post data that we can take a look at, and which two fields you are using to want to get the difference.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
