verwendete Formeln |
Zelle | Formel | Bereich |
B7 | =Feiertage!A1 | |
A10 | =B10 | |
B10 | =(D7&B7)*1 | |
E10 | =WENN(D10<C10;SUMME(D10-C10)*24+24;SUMME(D10-C10)*24)/24 | |
F10 | =E10*24 | |
G10 | =24*(MAX(;MIN(D10+(C10>D10);MAX(("21:00">"06:00");"06:00"))-MAX(C10;"21:00"))+MAX(;MIN(D10;MAX(("21:00">"06:00");"06:00"))-MAX(;"21:00"))*(C10>D10)+MAX(;MIN(D10+(C10>D10);MIN(("21:00">"06:00");"06:00"))-MAX(C10;))+MIN(D10;MIN(("21:00">"06:00");"06:00"))*(C10>D10)) | |
H10 | =WENN(WOCHENTAG($A10;2)=6;MAX(;MIN(H$5+(H$4>H$5);$D10+($C10>$D10))-MAX(H$4;$C10))+MAX(;(MIN(H$5;$D10+($C10>$D10))-$C10)*(H$4>H$5))+MAX(;MIN(H$5+(H$4>H$5);$D10+0)-H$4)*($C10>$D10);"") | |
I10 | =WENN(WOCHENTAG($A10;2)=7;MAX(;MIN(I$5+(I$4>I$5);$D10+($C10>$D10))-MAX(I$4;$C10))+MAX(;(MIN(I$5;$D10+($C10>$D10))-$C10)*(I$4>I$5))+MAX(;MIN(I$5+(I$4>I$5);$D10+0)-I$4)*($C10>$D10);"") | |
J10 | =WENN(ZÄHLENWENN(Feiertage;A10)=1;MAX(;MIN(J$5+(J$4>J$5);$D10+($C10>$D10))-MAX(J$4;$C10))+MAX(;(MIN(J$5;$D10+($C10>$D10))-$C10)*(J$4>J$5))+MAX(;MIN(J$5+(J$4>J$5);$D10+0)-J$4)*($C10>$D10);"") | |
A11 | =B11 | |
B11 | =WENN(B10="";"";WENN(MONAT(B10)=MONAT(B10+1);B10+1;"")) | |
E11 | =WENN(D11<C11;SUMME(D11-C11)*24+24;SUMME(D11-C11)*24)/24 | |
F11 | =E11*24 | |
G11 | =24*(MAX(;MIN(D11+(C11>D11);MAX(("21:00">"06:00");"06:00"))-MAX(C11;"21:00"))+MAX(;MIN(D11;MAX(("21:00">"06:00");"06:00"))-MAX(;"21:00"))*(C11>D11)+MAX(;MIN(D11+(C11>D11);MIN(("21:00">"06:00");"06:00"))-MAX(C11;))+MIN(D11;MIN(("21:00">"06:00");"06:00"))*(C11>D11)) | |
H11 | =WENN(WOCHENTAG($A11;2)=6;MAX(;MIN(H$5+(H$4>H$5);$D11+($C11>$D11))-MAX(H$4;$C11))+MAX(;(MIN(H$5;$D11+($C11>$D11))-$C11)*(H$4>H$5))+MAX(;MIN(H$5+(H$4>H$5);$D11+0)-H$4)*($C11>$D11);"") | |
I11 | =WENN(WOCHENTAG($A11;2)=7;MAX(;MIN(I$5+(I$4>I$5);$D11+($C11>$D11))-MAX(I$4;$C11))+MAX(;(MIN(I$5;$D11+($C11>$D11))-$C11)*(I$4>I$5))+MAX(;MIN(I$5+(I$4>I$5);$D11+0)-I$4)*($C11>$D11);"") | |
J11 | =WENN(ZÄHLENWENN(Feiertage;A11)=1;MAX(;MIN(J$5+(J$4>J$5);$D11+($C11>$D11))-MAX(J$4;$C11))+MAX(;(MIN(J$5;$D11+($C11>$D11))-$C11)*(J$4>J$5))+MAX(;MIN(J$5+(J$4>J$5);$D11+0)-J$4)*($C11>$D11);"") | |
A12 | =B12 | |
B12 | =WENN(B11="";"";WENN(MONAT(B11)=MONAT(B11+1);B11+1;"")) | |
E12 | =WENN(D12<C12;SUMME(D12-C12)*24+24;SUMME(D12-C12)*24)/24 | |
F12 | =E12*24 | |
G12 | =24*(MAX(;MIN(D12+(C12>D12);MAX(("21:00">"06:00");"06:00"))-MAX(C12;"21:00"))+MAX(;MIN(D12;MAX(("21:00">"06:00");"06:00"))-MAX(;"21:00"))*(C12>D12)+MAX(;MIN(D12+(C12>D12);MIN(("21:00">"06:00");"06:00"))-MAX(C12;))+MIN(D12;MIN(("21:00">"06:00");"06:00"))*(C12>D12)) | |
H12 | =WENN(WOCHENTAG($A12;2)=6;MAX(;MIN(H$5+(H$4>H$5);$D12+($C12>$D12))-MAX(H$4;$C12))+MAX(;(MIN(H$5;$D12+($C12>$D12))-$C12)*(H$4>H$5))+MAX(;MIN(H$5+(H$4>H$5);$D12+0)-H$4)*($C12>$D12);"") | |
I12 | =WENN(WOCHENTAG($A12;2)=7;MAX(;MIN(I$5+(I$4>I$5);$D12+($C12>$D12))-MAX(I$4;$C12))+MAX(;(MIN(I$5;$D12+($C12>$D12))-$C12)*(I$4>I$5))+MAX(;MIN(I$5+(I$4>I$5);$D12+0)-I$4)*($C12>$D12);"") | |
J12 | =WENN(ZÄHLENWENN(Feiertage;A12)=1;MAX(;MIN(J$5+(J$4>J$5);$D12+($C12>$D12))-MAX(J$4;$C12))+MAX(;(MIN(J$5;$D12+($C12>$D12))-$C12)*(J$4>J$5))+MAX(;MIN(J$5+(J$4>J$5);$D12+0)-J$4)*($C12>$D12);"") | |
A13 | =B13 | |
B13 | =WENN(B12="";"";WENN(MONAT(B12)=MONAT(B12+1);B12+1;"")) | |
E13 | =WENN(D13<C13;SUMME(D13-C13)*24+24;SUMME(D13-C13)*24)/24 | |
F13 | =E13*24 | |
G13 | =24*(MAX(;MIN(D13+(C13>D13);MAX(("21:00">"06:00");"06:00"))-MAX(C13;"21:00"))+MAX(;MIN(D13;MAX(("21:00">"06:00");"06:00"))-MAX(;"21:00"))*(C13>D13)+MAX(;MIN(D13+(C13>D13);MIN(("21:00">"06:00");"06:00"))-MAX(C13;))+MIN(D13;MIN(("21:00">"06:00");"06:00"))*(C13>D13)) | |
H13 | =WENN(WOCHENTAG($A13;2)=6;MAX(;MIN(H$5+(H$4>H$5);$D13+($C13>$D13))-MAX(H$4;$C13))+MAX(;(MIN(H$5;$D13+($C13>$D13))-$C13)*(H$4>H$5))+MAX(;MIN(H$5+(H$4>H$5);$D13+0)-H$4)*($C13>$D13);"") | |
I13 | =WENN(WOCHENTAG($A13;2)=7;MAX(;MIN(I$5+(I$4>I$5);$D13+($C13>$D13))-MAX(I$4;$C13))+MAX(;(MIN(I$5;$D13+($C13>$D13))-$C13)*(I$4>I$5))+MAX(;MIN(I$5+(I$4>I$5);$D13+0)-I$4)*($C13>$D13);"") | |
J13 | =WENN(ZÄHLENWENN(Feiertage;A13)=1;MAX(;MIN(J$5+(J$4>J$5);$D13+($C13>$D13))-MAX(J$4;$C13))+MAX(;(MIN(J$5;$D13+($C13>$D13))-$C13)*(J$4>J$5))+MAX(;MIN(J$5+(J$4>J$5);$D13+0)-J$4)*($C13>$D13);"") | |
A14 | =B14 | |
B14 | =WENN(B13="";"";WENN(MONAT(B13)=MONAT(B13+1);B13+1;"")) | |
E14 | =WENN(D14<C14;SUMME(D14-C14)*24+24;SUMME(D14-C14)*24)/24 | |
F14 | =E14*24 | |
G14 | =24*(MAX(;MIN(D14+(C14>D14);MAX(("21:00">"06:00");"06:00"))-MAX(C14;"21:00"))+MAX(;MIN(D14;MAX(("21:00">"06:00");"06:00"))-MAX(;"21:00"))*(C14>D14)+MAX(;MIN(D14+(C14>D14);MIN(("21:00">"06:00");"06:00"))-MAX(C14;))+MIN(D14;MIN(("21:00">"06:00");"06:00"))*(C14>D14)) | |
H14 | =WENN(WOCHENTAG($A14;2)=6;MAX(;MIN(H$5+(H$4>H$5);$D14+($C14>$D14))-MAX(H$4;$C14))+MAX(;(MIN(H$5;$D14+($C14>$D14))-$C14)*(H$4>H$5))+MAX(;MIN(H$5+(H$4>H$5);$D14+0)-H$4)*($C14>$D14);"") | |
I14 | =WENN(WOCHENTAG($A14;2)=7;MAX(;MIN(I$5+(I$4>I$5);$D14+($C14>$D14))-MAX(I$4;$C14))+MAX(;(MIN(I$5;$D14+($C14>$D14))-$C14)*(I$4>I$5))+MAX(;MIN(I$5+(I$4>I$5);$D14+0)-I$4)*($C14>$D14);"") | |
J14 | =WENN(ZÄHLENWENN(Feiertage;A14)=1;MAX(;MIN(J$5+(J$4>J$5);$D14+($C14>$D14))-MAX(J$4;$C14))+MAX(;(MIN(J$5;$D14+($C14>$D14))-$C14)*(J$4>J$5))+MAX(;MIN(J$5+(J$4>J$5);$D14+0)-J$4)*($C14>$D14);"") | |