Filter the Day column to remove (null) values.Home –> Remove Rows –> Remove Top Rows –> 2.Select the “Billy” worksheet (Ok, to be fair, it would probably be called Sheet1 in my template).Open a blank workbook –> Power Query –> From File –> From Excel.I’ll save the file in a folder, and get to work. I’m set up a template, email to Billy and get him to fill it out and email it to me every two weeks. Okay, first thing I’m going to do is set up a system. Formula work is subject to error, so why not essentially automate the solution? Using Power Query to Calculate Hours Worked Because I’m tired of having to actually write the formula every time Billy sends me his timesheet. If we can do this in Excel, why would we cook up a Power Query solution? Easy. There’s lots that would work, and this is one: Plumbing in that logic, we’d would need a different formula. Where I used to work (before I went out on my own), we had a rule that if you worked any more than 4 hours you MUST take a lunch break. (This allows us to convert the fractional time into a number that represents hours instead of fractions of a day.)Įasy enough, and the following submitted formula (copied down from F4:F9 and summed) works:Īlso, there was a great comment that Billy shouldn’t get paid for his lunch break. (This allows us to subtract the Start from the End and get the difference in hours. Test if the End time is less than the start time.The data includes End times that are intended to be the day following the Start time.The recognition that if you omit the day it defaults to, and.The recognition that all times are fractions of days,.Now, for anyone who has done this a long time, there a few key pieces to solving this: Background and Excel Formula SolutionĬhandoo provided a sample file on his blog, so I downloaded it. I thought I’d share a bit more detailed version of that here as I think many users still struggle with time in Excel. As the answers were pouring in, I decided to tackle the issue my own way too. It always blows my mind how many people respond on Chandoo’s blog. The first is that I’ve had to do this kind of stuff in the past, the second is because I’ve got a new toy I’d use to do it. This example resonated with me for a couple of reasons. Chandoo posted an interesting challenge on his blog last Friday, challenging users to calculated hours worked for an employee name Billy.
0 kommentar(er)
