Forum Discussion
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 "ti...
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 |
- 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!
