You may remember my posts from yesterday regarding the timehseet I am
creating. It all works beautifully now and I thought the hard bits were out
of the way as the time is seperating properly, rest breaks are deducted as
required. All really smooth.

I have come up with a real corker though, this is a real thinker. On a
Saturday the user gets paid single time for travel all day, 1.5x for work on
site in the morning and 2.0x for work on site in the afternoon. Morning and
afternoon are defined by different areas of the spreadsheet so dont worry
about actual time. I need two formulas, one for the 2.0x pay total and one
for the 1.5x pay total.

If six hours are worked then 30 minutes (1/48) needs deducting from the
total. Here's where it gets fun, if the six or more hours are done in both
the AM and PM areas then 15 minutes should be deducted from each 1.5x and
2.0x. If, however, the users works six hours just in the morning and nothing
in the afternoon then all 30 minutes should be deducted from 1.5x and
likewise if they dont work in the morning but work 6 or more hours in the
afternoon then 30 minutes should be decuted from 2.0x pay.

My problem is that the if someone does more than 12 hours split over AM and
PM, 7 hours in each for instance, then 30 minutes will be deducted from AM,
30 minutes from PM, and then 15 minutes from both as the conditions are being
met for all. This deducts 1 1/2 hours total instead of just 30 minutes, in
this case 15 minutes should have been taken from each and nothing else.

So, 1.5x total needs to say IF nothing worked in PM range AND 6 or more
hours worked in AM then deduct 30 minutes but if something worked in PM
aswell then deduct 15 minutes. The 2.0x total will then read the same but
vice versa so 15 minutes deducted from both.

Hope that makes some sense, you can see why its frying my brain.

Thanks in advance.