Negative hours

[Sean2]

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.

[Sean2 · Accepted solution]

Thank you both for your suggestions. What eneded up working for me was using a custom colmn

Duration.TotalMinutes(#time(0,0,0)+([End Time]-[Begin Time])-#time(0,0,0))

I found this answer on another post on here.

https://community.powerbi.com/t5/Desktop/Negative-Duration/m-p/250515#M111195

[dax]

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]

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])

[d_gosbell]

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