Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 tri...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    mahoneypat- That works great