Nov 16 2021 08:30 AM - edited Nov 16 2021 09:19 AM
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.
Nov 16 2021 08:52 AM
@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 |
Nov 16 2021 09:21 AM
Nov 16 2021 09:41 AM
@Tom_M_1969 I would use a helper column for each time entry and then SUMIF that one. See attached.
Nov 16 2021 09:59 AM
Nov 16 2021 11:09 AM - edited Nov 16 2021 11:27 AM
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.
Nov 16 2021 11:26 AM
Nov 16 2021 11:09 AM - edited Nov 16 2021 11:27 AM
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.