Forum Discussion

Sean2's avatar
Sean2
Helper I
6 years ago
Solved

Negative hours

Hi folks. I’m trying to figure out a solution to calculate hours worked on our time sheets. We have an entry for each day, with an in time and an out time on a 24-hour clock. The issue I’m having is occasionally people are required to work a shift from before midnight to after midnight. So, if for example, someone clocks in at 23:00 and clocks out at 1:00, PowerBI calculates that as negative 22 hours. Is there some kind of custom column I could create to get the right number of hours?

Any ideas would be appreciated.

Thanks.

Need Help

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    6 years ago

    In this case I think you need to use a datetime column, not just the time component when calculating the hours. 

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Sean2,

    You could try below measure to see whether it work or not.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ31DdUMLQyMAAiJR2EkJExVCxWJxqLqA6mZhSFhgboCo0UTIlVaAi3OxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [st = _t, et = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"st", type datetime}, {"et", type datetime}})
in
    #"Changed Type"
    Measure = DATEDIFF(MIN('Table'[st]),MIN('Table'[et]), HOUR)

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • d_gosbell's avatar
      d_gosbell
      Super User
      6 years ago

      Or you could just add a custom column which checks if the start is greater than end.

       

      Using the sample data from Zoe's post the custom column expression would be something like:

       

      if [st] > [et] then Duration.Hours([st] -[et]) else Duration.Hours([et] -[st])