work hours with lunch break

[black_salami]

Hello,

can someone help me change this formula so it would count work hours but substracting 15 min for lunch break?

=IF(OR(V$49<$C$50,V$49>$C$51),0, IF(V53="OFF",0,IF(X53="D",0,IF(X53="W",(GRAFIK!$E8),IF(X53="U",(+GRAFIK!$E8 ),IF(X53="C",(+GRAFIK!$E8),IF(X53="M",(+GRAFIK!$E8),IF(V53="",0,IF((W53-V53)<0,(1+(W53-V53))*24,(W53-V53)*24)))))))))

it works within attached file

regards

[offthelip]

The way your equation is written far more complicated than it needs to be.
Looking at the conditions there are only 4 different outcomes:
0
GRAFIK!E8
1+(W53-V53)*24
(W53-V53)*24

So when writing your If statements you should identify the full condition for each of these four cases in one logical statement, if at all possible. Most of your tests for each condition are in sequence so they can be combined together. The zero outcome can occur in two places so must be checked twice.
So the condition for the first zero is
OR(V$49<$C$50,V$49>$C$51, V53="OFF",X53="D")
If this isn’t true then
The condition for GRAFIK!E8 is
Or(X53="W",X53="U",X53="C",X53="M")
Then if this isn’t true we check zero again
V53=""
Then we just have the last if statement
IF((W53-V53)<0,(1+(W53-V53))*24,(W53-V53)*24

So I reckon the whole equation as per the origianl should be:
If (OR(V$49<$C$50,V$49>$C$51,V53="OFF",X53="D"),0,if(Or(X53="W",X53="U",X53="C ",X53="M"), GRAFIK!$E8,if(V53="",0, IF((W53-V53)<0,(1+(W53-V53))*24,(W53-V53)*24))))

To change the calculation to deduct 15 minute for lucnh, I presume you mean just in (w53-V53) bits it would become:

If (OR(V$49<$C$50,V$49>$C$51,V53="OFF",X53="D"),0,if(Or(X53="W",X53="U",X53="C ",X53="M"), GRAFIK!$E8,if(V53="",0, IF((W53-V53)<0,(0.75+(W53-V53))*24,(W53-V53-0.25)*24))))

Note I had a look in your workbook but I couldn't find the equations so I ahven't tested that it is correct

Note: if you never get the case when V53="" and X53=W,U,C or M , then you could combine the zero test for V53="" in the first Or statement. So you can get it down to three if statements instead of four.

[snb]

=(Z$49>$C$50)*(Z$49<$C$51) * (Z53<>"OFF") *(NOT(ISERR(SEARCH(AB53;"DWUCM"))*GRAFIK!$E8) * (Z53<>"") *(((AA53-Z53)<0)+(AA53-Z53))*24)