SOLVED

Converting time into 2-hour billing units

Occasional Contributor

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.

6 Replies

@Tom_M_1969 And what about the second entry for John that spans 122 hours?

John11/15/21 2:30 AM11/20/21 4:30 AM122:00
Sorry, 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.

@Tom_M_1969 I would use a helper column for each time entry and then SUMIF that one. See attached.

 

@Riny_van_Eekelen That looks like it worked perfectly for John, but I'm not sure why Sally is showing zero units when she worked 2.5 hours on 11/15 and 2.5 hours on 11/16. She should have achieved 1 unit on each of those days. Also, Tom achieved 2 units because he worked 4 hours on 11/16, but in column J he's only showing 1 unit.
Please let me know if I'm missing something. Thank you for your assistance!
best response confirmed by Tom_M_1969 (Occasional Contributor)
Solution

@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.

@Riny_van_Eekelen That works great. Thank you!