Forum Discussion
Converting time into 2-hour billing units
I'm using Microsoft® Excel® for Microsoft 365 MSO (Version 2110 Build 16.0.14527.20234) 64-bit on a Windows 10 PC.
In the attached spreadsheet, start and and times are being used to calculate "time worked" in column E. Each employee's total time worked is then added up in column I.
However, I need to convert the time worked into 2-hour billing units in column J. If an employee works 2 or more hours in one day, that counts as 1 billing unit. If they work 4 or more hours in one day, that's 2 units. (4 hours is the maximum that a single employee can work in one day, so only 2 billing units can be earned in a single day).
The tricky part is that time cannot be carried over into the next day. If Tom works one hour on one day and one hour on the next day, that should not add up to any billing units. In the attached spreadsheet, John achieved 2 billing units on 11/15 because he worked 4 hours. He achieved zero billing units on 11/16 because he only worked an hour and a half that day.
I've tried to get this to work a dozen ways and still haven't had any luck. Any suggestions would be greatly appreciated. Thanks.
Tom_M_1969 Ok, now I get it. A bit slow, sorry!
I favour using built-in tools to summarise data. Therefore, I chose a pivot table to summarise the time records per person per day. Then, I added a helper column to calculate the units per person per day, which are then summarised in the table in column H:I.
Not the most elegant solution. If I had more time, I would probably come up with something better, but not right now. Sorry.
6 Replies
- Riny_van_EekelenPlatinum Contributor
Tom_M_1969 And what about the second entry for John that spans 122 hours?
John 11/15/21 2:30 AM 11/20/21 4:30 AM 122:00 - Tom_M_1969Copper ContributorSorry, that was a mistake. No one will ever sign in on one day and sign out on a different day. I uploaded a new test file.
- Riny_van_EekelenPlatinum Contributor
Tom_M_1969 I would use a helper column for each time entry and then SUMIF that one. See attached.