Cell needs IF AND, with SUM of different cells to IF AND cells - Confused? I am

[tammorice]

Hi

I have a table where $M11=IF(AND($K$3="CASUAL",$F11<TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]I[/TD]
[TD]L[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]shift time[/TD]
[TD]break[/TD]
[TD]break[/TD]
[TD]total paid[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]8:30[/TD]
[TD]0:30[/TD]
[TD]0:15[/TD]
[TD]7:45[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1:45[/TD]
[TD][/TD]
[TD][/TD]
[TD]2:00[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]K3 "CASUAL"[/TD]
[/TR]
</tbody>[/TABLE]

So all is good if the shift is less than 2hrs then the total paid is increased to 2hrs.

Any shift can be recorded on any row. so I need to be able to also deduct breaks from the total.

Can anyone guide me on how I can add the sum SUM(F11-I11-L11) to the formula above in M11:M50

Super appreciate your help.

 

[steve the fish]

Im not entirely sure what you are after but perhaps try this:

=IF(AND($K$3="casual",F11>0),MAX(2/24,F11-I11-L11),0)

 

[tammorice]

Im not entirely sure what you are after but perhaps try this:

=IF(AND($K$3="casual",F11>0),MAX(2/24,F11-I11-L11),0)

Hey Steve the Fish, Thanks for such a quick response.

What I'm after is for these 2 formulas to calculate an answer in the one cell.

=IF(AND($K$3="CASUAL",$F11<TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)
=SUM(F11-I11-L11)

Row 11 is used for 2 different calcs.
1. a job (using drop down) with less than 2 hours (F11), that gets paid a minimum of 2hrs (M11), if more than 2 hours (F11), gets paid amount in F11.
2. a job (using drop down) with more than 2 hours (F11), that has breaks to be deducted (I11 and L11), that gets paid (shifthrs-breaks) (M11)

 

[tammorice]

Hey Steve the Fish, Thanks for such a quick response.

What I'm after is for these 2 formulas to calculate an answer in the one cell.

=IF(AND($K$3="CASUAL",$F11<timevalue("02:00"),$f11>TIMEVALUE("00:00")),"02:00",$F11)
=SUM(F11-I11-L11)</timevalue("02:00"),$f11>

Row 11 is used for 2 different calcs.
1. a job (using drop down) with less than 2 hours (F11), that gets paid a minimum of 2hrs (M11), if more than 2 hours (F11), gets paid amount in F11.
2. a job (using drop down) with more than 2 hours (F11), that has breaks to be deducted (I11 and L11), that gets paid (shifthrs-breaks) (M11)

I just realised my IF formula is wrong. oops

it should read:

=IF(AND($L$5="CASUAL",$F11<<timevalue("02:00"),$f11>TIMEVALUE("02:00"),$F11>TIMEVALUE("00:00")),"02:00",$F11)</timevalue("02:00"),$f11>

 

[steve the fish]

Did you try what i suggested? Does that not do what you ask for?

 

[tammorice]

Did you try what i suggested? Does that not do what you ask for?

No. Unfortunately, it returned "00:00".

It's time to have another look at this one (It was driving me crazy - so I backed away for a bit ) cause the basic vlookup i just did doesn't want to play either.

 

[steve the fish]

Thats because either K3 isnt 'casual' or F11 isnt > 0.