Microsoft Excel

Herbers Excel/VBA-Archiv

KW Tag oder Nacht | Herbers Excel-Forum


Betrifft: KW Tag oder Nacht von: Alois G
Geschrieben am: 28.02.2010 16:04:23

Hallo Excel Experten.

Ich versuche anhand der Kalenderwoche meine Schicht zu ermitteln. Tag oder Nacht. Das geht relativ einfach über gerade oder ungerade Wochenzahl. Wenn da nicht die Woche 53 wäre. Damit verschiebt sich die Tag Woche von gerade auf ungerade Wochenzahl. Und schon muss sich wieder von Hand etwas nachkorrigieren. Wie kann ich diesen Fehler beheben und sicher sein das Excel mir richtig angezeigt. Tag oder Nachtschicht.
Dieses und auch die nächsten Jahre?
Gibt es da eine Formellösung ohne VBA.
Bitte um eure Hilfe
Gruß Alois

  

Betrifft: Bsp? o.T. von: Christian
Geschrieben am: 28.02.2010 16:07:05




  

Betrifft: AW: Bsp? o.T. von: Alois G
Geschrieben am: 28.02.2010 16:45:04

Okay

Hier ein Beispiel

https://www.herber.de/bbs/user/68281.xls

Gruß Alois


  

Betrifft: KW 53 von: Christian
Geschrieben am: 28.02.2010 16:51:49

Hallo Alois,

verstehe zwar die Logik nicht ganz, aber Vorschlag:

Tabelle1

 ABC
1DatumKWTAG/Nacht
201.01.201053Nachtschicht

Formeln der Tabelle
ZelleFormel
C2=WENN(B2=53;"Nachtschicht";WENN(REST(B2;2)<>0;"Tagschicht";"Nachtschicht"))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

MfG Christian


  

Betrifft: AW: Bsp? o.T. von: Hajo_Zi
Geschrieben am: 28.02.2010 16:52:21

Hallo Alois,

lege ein Stardatum fest.

Tabelle1

 ABC
1DatumKWTAG/Nacht
205.10.200941Nachtschicht
312.10.200942Tagschicht
419.10.200943Nachtschicht
526.10.200944Tagschicht
602.11.200945Nachtschicht
709.11.200946Tagschicht
816.11.200947Nachtschicht
923.11.200948Tagschicht
1030.11.200949Nachtschicht
1107.12.200950Tagschicht
1214.12.200951Nachtschicht
1321.12.200952Tagschicht
1428.12.200953Nachtschicht
1504.01.20101Tagschicht

verwendete Formeln
Zelle Formel
B2 =WENN((A2)="";"";KÜRZEN((A2-WOCHENTAG(A2;2)-DATUM(JAHR(A2+4-WOCHENTAG(A2;2));1;-10))/7))
C2 =WENN(REST("05.10.2009"-A2;14)<>0;"Tagschicht";"Nachtschicht")
A3 =A2+7
B3 =WENN((A3)="";"";KÜRZEN((A3-WOCHENTAG(A3;2)-DATUM(JAHR(A3+4-WOCHENTAG(A3;2));1;-10))/7))
C3 =WENN(REST("05.10.2009"-A3;14)<>0;"Tagschicht";"Nachtschicht")
A4 =A3+7
B4 =WENN((A4)="";"";KÜRZEN((A4-WOCHENTAG(A4;2)-DATUM(JAHR(A4+4-WOCHENTAG(A4;2));1;-10))/7))
C4 =WENN(REST("05.10.2009"-A4;14)<>0;"Tagschicht";"Nachtschicht")
A5 =A4+7
B5 =WENN((A5)="";"";KÜRZEN((A5-WOCHENTAG(A5;2)-DATUM(JAHR(A5+4-WOCHENTAG(A5;2));1;-10))/7))
C5 =WENN(REST("05.10.2009"-A5;14)<>0;"Tagschicht";"Nachtschicht")
A6 =A5+7
B6 =WENN((A6)="";"";KÜRZEN((A6-WOCHENTAG(A6;2)-DATUM(JAHR(A6+4-WOCHENTAG(A6;2));1;-10))/7))
C6 =WENN(REST("05.10.2009"-A6;14)<>0;"Tagschicht";"Nachtschicht")
A7 =A6+7
B7 =WENN((A7)="";"";KÜRZEN((A7-WOCHENTAG(A7;2)-DATUM(JAHR(A7+4-WOCHENTAG(A7;2));1;-10))/7))
C7 =WENN(REST("05.10.2009"-A7;14)<>0;"Tagschicht";"Nachtschicht")
A8 =A7+7
B8 =WENN((A8)="";"";KÜRZEN((A8-WOCHENTAG(A8;2)-DATUM(JAHR(A8+4-WOCHENTAG(A8;2));1;-10))/7))
C8 =WENN(REST("05.10.2009"-A8;14)<>0;"Tagschicht";"Nachtschicht")
A9 =A8+7
B9 =WENN((A9)="";"";KÜRZEN((A9-WOCHENTAG(A9;2)-DATUM(JAHR(A9+4-WOCHENTAG(A9;2));1;-10))/7))
C9 =WENN(REST("05.10.2009"-A9;14)<>0;"Tagschicht";"Nachtschicht")
A10 =A9+7
B10 =WENN((A10)="";"";KÜRZEN((A10-WOCHENTAG(A10;2)-DATUM(JAHR(A10+4-WOCHENTAG(A10;2));1;-10))/7))
C10 =WENN(REST("05.10.2009"-A10;14)<>0;"Tagschicht";"Nachtschicht")
A11 =A10+7
B11 =WENN((A11)="";"";KÜRZEN((A11-WOCHENTAG(A11;2)-DATUM(JAHR(A11+4-WOCHENTAG(A11;2));1;-10))/7))
C11 =WENN(REST("05.10.2009"-A11;14)<>0;"Tagschicht";"Nachtschicht")
A12 =A11+7
B12 =WENN((A12)="";"";KÜRZEN((A12-WOCHENTAG(A12;2)-DATUM(JAHR(A12+4-WOCHENTAG(A12;2));1;-10))/7))
C12 =WENN(REST("05.10.2009"-A12;14)<>0;"Tagschicht";"Nachtschicht")
A13 =A12+7
B13 =WENN((A13)="";"";KÜRZEN((A13-WOCHENTAG(A13;2)-DATUM(JAHR(A13+4-WOCHENTAG(A13;2));1;-10))/7))
C13 =WENN(REST("05.10.2009"-A13;14)<>0;"Tagschicht";"Nachtschicht")
A14 =A13+7
B14 =WENN((A14)="";"";KÜRZEN((A14-WOCHENTAG(A14;2)-DATUM(JAHR(A14+4-WOCHENTAG(A14;2));1;-10))/7))
C14 =WENN(REST("05.10.2009"-A14;14)<>0;"Tagschicht";"Nachtschicht")
A15 =A14+7
B15 =WENN((A15)="";"";KÜRZEN((A15-WOCHENTAG(A15;2)-DATUM(JAHR(A15+4-WOCHENTAG(A15;2));1;-10))/7))
C15 =WENN(REST("05.10.2009"-A15;14)<>0;"Tagschicht";"Nachtschicht")

Tabellendarstellung in Foren Version 5.16



GrußformelHomepage


  

Betrifft: AW: Bsp? o.T. von: Alois G
Geschrieben am: 28.02.2010 19:47:27

Danke Für den Tipp Hajo.

Nur leider rechnet diese Formel nur Montage richtig.

Wenn das Datum einen anderen Wochentag erwischt, ist das Ergebniss falsch.

Woran liegt das? Läst sich das anpassen?

Gruß Alois


  

Betrifft: AW: Bsp? o.T. von: Alois G
Geschrieben am: 28.02.2010 19:54:28

Oh da war ich zu schnell.

Liegt natürlich am Startdatum.

Hab's kapiert.

Danke für die Hilfe.

Gruß Alois


  

Betrifft: AW: Bsp? o.T. von: Hajo_Zi
Geschrieben am: 28.02.2010 20:01:08

Hallo Alois,

Du mußt den Ausgangstag dann auch auf den anderen Tag anpassen. Ich habe die Formel angepast.

Tabelle1

 ADEHILMPQT
1DatumTAG/NachtDatumTAG/NachtDatumTAG/NachtDatumTAG/NachtDatumTAG/Nacht
205.10.09Nachtschicht06.10.09Nachtschicht07.10.09Nachtschicht08.10.09Nachtschicht09.10.09Nachtschicht
312.10.09Tagschicht13.10.09Tagschicht14.10.09Tagschicht15.10.09Tagschicht16.10.09Tagschicht
419.10.09Nachtschicht20.10.09Nachtschicht21.10.09Nachtschicht22.10.09Nachtschicht23.10.09Nachtschicht
526.10.09Tagschicht27.10.09Tagschicht28.10.09Tagschicht29.10.09Tagschicht30.10.09Tagschicht
602.11.09Nachtschicht03.11.09Nachtschicht04.11.09Nachtschicht05.11.09Nachtschicht06.11.09Nachtschicht
709.11.09Tagschicht10.11.09Tagschicht11.11.09Tagschicht12.11.09Tagschicht13.11.09Tagschicht
816.11.09Nachtschicht17.11.09Nachtschicht18.11.09Nachtschicht19.11.09Nachtschicht20.11.09Nachtschicht
923.11.09Tagschicht24.11.09Tagschicht25.11.09Tagschicht26.11.09Tagschicht27.11.09Tagschicht
1030.11.09Nachtschicht01.12.09Nachtschicht02.12.09Nachtschicht03.12.09Nachtschicht04.12.09Nachtschicht
1107.12.09Tagschicht08.12.09Tagschicht09.12.09Tagschicht10.12.09Tagschicht11.12.09Tagschicht
1214.12.09Nachtschicht15.12.09Nachtschicht16.12.09Nachtschicht17.12.09Nachtschicht18.12.09Nachtschicht
1321.12.09Tagschicht22.12.09Tagschicht23.12.09Tagschicht24.12.09Tagschicht25.12.09Tagschicht
1428.12.09Nachtschicht29.12.09Nachtschicht30.12.09Nachtschicht31.12.09Nachtschicht01.01.10Nachtschicht
1504.01.10Tagschicht05.01.10Tagschicht06.01.10Tagschicht07.01.10Tagschicht08.01.10Tagschicht

verwendete Formeln
Zelle Formel
D2 =WENN(REST(A$2-A2;14)<>0;"Tagschicht";"Nachtschicht")
E2 =A2+1
H2 =WENN(REST(E$2-E2;14)<>0;"Tagschicht";"Nachtschicht")
I2 =E2+1
L2 =WENN(REST(I$2-I2;14)<>0;"Tagschicht";"Nachtschicht")
M2 =I2+1
P2 =WENN(REST(M$2-M2;14)<>0;"Tagschicht";"Nachtschicht")
Q2 =M2+1
T2 =WENN(REST(Q$2-Q2;14)<>0;"Tagschicht";"Nachtschicht")
A3 =A2+7
D3 =WENN(REST(A$2-A3;14)<>0;"Tagschicht";"Nachtschicht")
E3 =E2+7
H3 =WENN(REST(E$2-E3;14)<>0;"Tagschicht";"Nachtschicht")
I3 =I2+7
L3 =WENN(REST(I$2-I3;14)<>0;"Tagschicht";"Nachtschicht")
M3 =M2+7
P3 =WENN(REST(M$2-M3;14)<>0;"Tagschicht";"Nachtschicht")
Q3 =Q2+7
T3 =WENN(REST(Q$2-Q3;14)<>0;"Tagschicht";"Nachtschicht")
A4 =A3+7
D4 =WENN(REST(A$2-A4;14)<>0;"Tagschicht";"Nachtschicht")
E4 =E3+7
H4 =WENN(REST(E$2-E4;14)<>0;"Tagschicht";"Nachtschicht")
I4 =I3+7
L4 =WENN(REST(I$2-I4;14)<>0;"Tagschicht";"Nachtschicht")
M4 =M3+7
P4 =WENN(REST(M$2-M4;14)<>0;"Tagschicht";"Nachtschicht")
Q4 =Q3+7
T4 =WENN(REST(Q$2-Q4;14)<>0;"Tagschicht";"Nachtschicht")
A5 =A4+7
D5 =WENN(REST(A$2-A5;14)<>0;"Tagschicht";"Nachtschicht")
E5 =E4+7
H5 =WENN(REST(E$2-E5;14)<>0;"Tagschicht";"Nachtschicht")
I5 =I4+7
L5 =WENN(REST(I$2-I5;14)<>0;"Tagschicht";"Nachtschicht")
M5 =M4+7
P5 =WENN(REST(M$2-M5;14)<>0;"Tagschicht";"Nachtschicht")
Q5 =Q4+7
T5 =WENN(REST(Q$2-Q5;14)<>0;"Tagschicht";"Nachtschicht")
A6 =A5+7
D6 =WENN(REST(A$2-A6;14)<>0;"Tagschicht";"Nachtschicht")
E6 =E5+7
H6 =WENN(REST(E$2-E6;14)<>0;"Tagschicht";"Nachtschicht")
I6 =I5+7
L6 =WENN(REST(I$2-I6;14)<>0;"Tagschicht";"Nachtschicht")
M6 =M5+7
P6 =WENN(REST(M$2-M6;14)<>0;"Tagschicht";"Nachtschicht")
Q6 =Q5+7
T6 =WENN(REST(Q$2-Q6;14)<>0;"Tagschicht";"Nachtschicht")
A7 =A6+7
D7 =WENN(REST(A$2-A7;14)<>0;"Tagschicht";"Nachtschicht")
E7 =E6+7
H7 =WENN(REST(E$2-E7;14)<>0;"Tagschicht";"Nachtschicht")
I7 =I6+7
L7 =WENN(REST(I$2-I7;14)<>0;"Tagschicht";"Nachtschicht")
M7 =M6+7
P7 =WENN(REST(M$2-M7;14)<>0;"Tagschicht";"Nachtschicht")
Q7 =Q6+7
T7 =WENN(REST(Q$2-Q7;14)<>0;"Tagschicht";"Nachtschicht")
A8 =A7+7
D8 =WENN(REST(A$2-A8;14)<>0;"Tagschicht";"Nachtschicht")
E8 =E7+7
H8 =WENN(REST(E$2-E8;14)<>0;"Tagschicht";"Nachtschicht")
I8 =I7+7
L8 =WENN(REST(I$2-I8;14)<>0;"Tagschicht";"Nachtschicht")
M8 =M7+7
P8 =WENN(REST(M$2-M8;14)<>0;"Tagschicht";"Nachtschicht")
Q8 =Q7+7
T8 =WENN(REST(Q$2-Q8;14)<>0;"Tagschicht";"Nachtschicht")
A9 =A8+7
D9 =WENN(REST(A$2-A9;14)<>0;"Tagschicht";"Nachtschicht")
E9 =E8+7
H9 =WENN(REST(E$2-E9;14)<>0;"Tagschicht";"Nachtschicht")
I9 =I8+7
L9 =WENN(REST(I$2-I9;14)<>0;"Tagschicht";"Nachtschicht")
M9 =M8+7
P9 =WENN(REST(M$2-M9;14)<>0;"Tagschicht";"Nachtschicht")
Q9 =Q8+7
T9 =WENN(REST(Q$2-Q9;14)<>0;"Tagschicht";"Nachtschicht")
A10 =A9+7
D10 =WENN(REST(A$2-A10;14)<>0;"Tagschicht";"Nachtschicht")
E10 =E9+7
H10 =WENN(REST(E$2-E10;14)<>0;"Tagschicht";"Nachtschicht")
I10 =I9+7
L10 =WENN(REST(I$2-I10;14)<>0;"Tagschicht";"Nachtschicht")
M10 =M9+7
P10 =WENN(REST(M$2-M10;14)<>0;"Tagschicht";"Nachtschicht")
Q10 =Q9+7
T10 =WENN(REST(Q$2-Q10;14)<>0;"Tagschicht";"Nachtschicht")
A11 =A10+7
D11 =WENN(REST(A$2-A11;14)<>0;"Tagschicht";"Nachtschicht")
E11 =E10+7
H11 =WENN(REST(E$2-E11;14)<>0;"Tagschicht";"Nachtschicht")
I11 =I10+7
L11 =WENN(REST(I$2-I11;14)<>0;"Tagschicht";"Nachtschicht")
M11 =M10+7
P11 =WENN(REST(M$2-M11;14)<>0;"Tagschicht";"Nachtschicht")
Q11 =Q10+7
T11 =WENN(REST(Q$2-Q11;14)<>0;"Tagschicht";"Nachtschicht")
A12 =A11+7
D12 =WENN(REST(A$2-A12;14)<>0;"Tagschicht";"Nachtschicht")
E12 =E11+7
H12 =WENN(REST(E$2-E12;14)<>0;"Tagschicht";"Nachtschicht")
I12 =I11+7
L12 =WENN(REST(I$2-I12;14)<>0;"Tagschicht";"Nachtschicht")
M12 =M11+7
P12 =WENN(REST(M$2-M12;14)<>0;"Tagschicht";"Nachtschicht")
Q12 =Q11+7
T12 =WENN(REST(Q$2-Q12;14)<>0;"Tagschicht";"Nachtschicht")
A13 =A12+7
D13 =WENN(REST(A$2-A13;14)<>0;"Tagschicht";"Nachtschicht")
E13 =E12+7
H13 =WENN(REST(E$2-E13;14)<>0;"Tagschicht";"Nachtschicht")
I13 =I12+7
L13 =WENN(REST(I$2-I13;14)<>0;"Tagschicht";"Nachtschicht")
M13 =M12+7
P13 =WENN(REST(M$2-M13;14)<>0;"Tagschicht";"Nachtschicht")
Q13 =Q12+7
T13 =WENN(REST(Q$2-Q13;14)<>0;"Tagschicht";"Nachtschicht")
A14 =A13+7
D14 =WENN(REST(A$2-A14;14)<>0;"Tagschicht";"Nachtschicht")
E14 =E13+7
H14 =WENN(REST(E$2-E14;14)<>0;"Tagschicht";"Nachtschicht")
I14 =I13+7
L14 =WENN(REST(I$2-I14;14)<>0;"Tagschicht";"Nachtschicht")
M14 =M13+7
P14 =WENN(REST(M$2-M14;14)<>0;"Tagschicht";"Nachtschicht")
Q14 =Q13+7
T14 =WENN(REST(Q$2-Q14;14)<>0;"Tagschicht";"Nachtschicht")
A15 =A14+7
D15 =WENN(REST(A$2-A15;14)<>0;"Tagschicht";"Nachtschicht")
E15 =E14+7
H15 =WENN(REST(E$2-E15;14)<>0;"Tagschicht";"Nachtschicht")
I15 =I14+7
L15 =WENN(REST(I$2-I15;14)<>0;"Tagschicht";"Nachtschicht")
M15 =M14+7
P15 =WENN(REST(M$2-M15;14)<>0;"Tagschicht";"Nachtschicht")
Q15 =Q14+7
T15 =WENN(REST(Q$2-Q15;14)<>0;"Tagschicht";"Nachtschicht")

Tabellendarstellung in Foren Version 5.16



Gruß Hajo


  

Betrifft: AW: Bsp? o.T. von: Alois G
Geschrieben am: 28.02.2010 20:29:12

Danke
Soviel Aufwand wäre für mich doch nicht nötig gewesen.

Mit dem kleinen Zusatz sieht meine Formel jetzt so aus

=WENN(REST(GANZZAHL(($Q$1+WOCHENTAG(L2;3))-L2);14)<>0;"Nachtschicht";"Tagschicht")

$Q$1 ist das Startdatum

in L2 steht das Datum für das ich "Nachtschicht"oder"Tagschicht" wissen will.

So geht's automatisch.

Da lieg ich doch richtig?


Danke nochmal.

Gruß Alois


  

Betrifft: AW: Bsp? o.T. von: Josef Ehrensberger
Geschrieben am: 28.02.2010 16:59:14

Hallo Alois,


dazu musst du eine Refferenzschicht definieren.



 ABCDEFGHIJK
1DatumKWTag/Nacht    Refferenz:01.01.2010TagschichtNachtschicht
205.10.201040Tagschicht        

Formeln der Tabelle
ZelleFormel
C2=WENN(REST(GANZZAHL((A2-($I$1-WOCHENTAG($I$1;3)))/7); 2)=0;$J$1;$K$1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4





Gruß Sepp