Forum Discussion

Tom_M_1969's avatar
Tom_M_1969
Copper Contributor
Nov 16, 2021
Solved

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

Resources