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 "ti...
excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 16, 2021

    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's avatar
Tom_M_1969
Copper Contributor
Nov 16, 2021
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!
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Nov 16, 2021

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.

Resources