IF,AND and OR

[Pyrite]

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.

 

[Pyrite]

OK, I have made a little progress. I have started with IF(AND((sum of travel
finish - sum of travel start)+(sum of site finish - sum of site start)>=6
hours,(site pm finish - site pm start)<=0),(site am finish - site am
start)-30 minutes,

This is where it gets hard, that basically says if there is more than six
hours worked through the whole saturday but nothing in the PM then enter the
site time from the am into the 1.5x column minus 30 minutes break.

The false condition of the original IF gets hard, the problem with the AND
function is that it returns false if either are false, so at the minute the
formula does not know if there is less than six hours worked or nothing
worked in the pm, this makes a difference though. There are three possible
combinations for this false condtion:

More than 6 hours worked, something in the PM = minus 15 minutes from am
site time

Less than 6 hours worked, something worked in the PM = carry forward total
am site time minus nothing

Less than 6 hours worked, nothing worked in the PM = carry forward total am
site time minus nothing.

I dont know how to make the formula figure out which of these is the
condition being met, I am getting totally tied up in knots in my head
thinking about multiple IF functions or maybe and AND OR combination, I dont
know......

 

[Pyrite]

I realise that once again I am answering my own posts but I figure people may
be able to either check my solution for glaring errors or maybe even use it
in their own projects if it meets needs. I realised that two of the options
had the same result so if I could seperate the third option and have that as
the true condition then the false would be correct for both. The complete
formula is as follows:

IF(AND(((sum of travel
finish - sum of travel start)+(sum of site finish - sum of site start))>=6
hours,(site pm finish - site pm start)<=0),(site am finish - site am
start)-30 minutes,IF(AND(((sum of travel finish - sum of travel start)+(sum
of site finish - sum of site start))>=6 hours,(sum of site pm finish - sum of
site pm start)>0),(sum of site am finish - sum of site am start)-15
minutes,(sum of site am finish- sum of site pm start)))

It probably is a little hard to follow, but simply if 6 hours or more are
worked then a break is recorded but only 15 minutes from this column if
anything has been worked in the afternoon and 30 minutes if nothing has been
worked in the afternoon. If under 6 hours has been worked nothing is deducted.

I am a little proud of this one, I'm self taught and this feels like a
MASSIVE step, although I imagine it is probably quite simple /