Forum Discussion
Converting time into 2-hour billing units
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.
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_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!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.