cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sean2 Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Sean2 Frequent Visitor
Frequent Visitor

Re: Negative hours

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

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Negative hours

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

Community Support Team
Community Support Team

Re: Negative hours

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.

 

 

Super User
Super User

Re: Negative hours

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

Sean2 Frequent Visitor
Frequent Visitor

Re: Negative hours

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

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)