How to work out number of 'days' worked based on total hours

I'm not sure how you arrive at those breakdowns in your two examples. For the first example, with a 12-hour day the first day would be 8 + 2 + 2 and the second day would be 8 + 1.5 + 0, so your totals should be 16 x base, 3.5 x OT15 and 2 x OT2. The second example does follow the same reasoning.

Please clarify.

Pete

OK, I spent some more time and worked it out, I got it!

Let's use the first example I got above. 21.5 hours
Firstly, I converted hours to decimals by dividing by twelve. = 1.79
The decimal points of the resulting number:
8 hours: 0.67 (8/12)
10 hours: 0.83 (10/12)
12 hours: 1 (12/12)

I then used if/and formulas to return me the value that falls within each band, and add the integer of the number to get me my full days.

Base: 16 hours
OT15: 3.5 hours
OT2: 2 hours

Originally Posted by Pete_UK

For the first example, ...

Hi Pete, you're right - typo - fixed, and solved!

OK, this is how I worked it out with Excel formulae - assume the total hours are in A2, with column B for Basic hours, column C for OT15, and column D for OT2, then these formulae can be put in the cells stated:

B2: =8*INT($A2/12)+MIN(8,MOD($A2,12))

C2: =2*INT($A2/12)+MAX(MOD($A2,12)-8,0)

D2: =2*INT($A2/12)+MAX(MOD($A2,12)-10,0)

Put other totals in A3, A4 etc., then copy the formulae down.

Hope this helps.

Pete