Microsoft Excel

Herbers Excel/VBA-Archiv

Zelle färben wenn / Arbeitstage eintrage

Betrifft: Zelle färben wenn / Arbeitstage eintrage von: JensBrest
Geschrieben am: 07.10.2020 02:49:16

Hallo liebe Excel Profis, ich habe einen uralten Turnusplan für meine Arbeit. Bisher mussten die Kollegen immer alles von Hand eintragen und ich möchte das ein bisschen vereinfachen. Dazu habe ich zwei Fragen


Frage 1:
Ich möchte, wenn eine Zelle H3:AL3 ein Sonntag (So) ist, dann soll sich die Zelle direkt darüber rot färben

Frage 2: Meine Turnus sieht so aus, 3 Tage Tagschicht, 3 Tage Spätschicht und 4 Tage frei.
Ist es möglich, dass Excel das immer automatisch ausfüllt alle 10 Tage pro Blatt/Monat, auch wenn ich das Jahr änder? (Die Tage ändern sich automatisch wenn ich das Jahr ändern)
Mein Turnus geht vom 01.01-10.01 und fängt dann wieder neu an. Pro Blatt habe ich einen Monat in dem die Arbeitstage eingetragen werden
Beispiel:

Januar

 HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
212345678910111213141516171819202122232425262728293031
3MoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMi
4                               
5                               
6FRFRFRSPSPSP    FRFRFRSPSPSP    FRFRFRSPSPSP    FR
7                               


Februar

 HIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
212345678910111213141516171819202122232425262728
3DoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMi
4                            
5                            
6FRFRSPSPSP    FRFRFRSPSPSP    FRFRFRSPSPSP   
7                            

Betrifft: AW: Zelle färben wenn / Arbeitstage eintrage
von: Matthias L
Geschrieben am: 07.10.2020 04:00:56

Hi

Zu Frage 1:
Ich möchte, wenn eine Zelle H3:AL3 ein Sonntag (So) ist, dann soll sich die Zelle direkt darüber rot färben

Aaaha! Warum zeigst Du dann im Bsp. den "Sa" rot an?


Da ja der 1.Jan bei Dir ein Mo. ist handelt es sich z.B. um das Jahr(2018) o. (2024) o. (2029) u.sw.


Tabelle1

 JKLM
1Februar

Formeln der Tabelle
ZelleFormel
J1=DATUM(H1;2;1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Tabelle1

 H
12026
201
3So

Formeln der Tabelle
ZelleFormel
H2=J1
H3=H2

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
H21. / Formel ist =WOCHENTAG(H2;2)=7Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Matthias

Betrifft: AW: Montag den 1.1. gibts erst wieder 2024 ...
von: neopa C
Geschrieben am: 07.10.2020 14:48:51

Hallo Jens,

... der 1.1.20 war ein Mi und der 1.1.21 ist ein Fr. Dein Januar Monatsblatt stammt aus welchem Jahr? Oder stehen in Zeile 2/3 bei Dir keine echte Exceldatumswerte?

Beschreibe also genauer, wann Dein 10-Tage Turnus mit was beginnt oder wann er begonnen hat und ob dieser Turnus auch über einen Jahreswechsel fortgesetzt wird bzw. werden soll. Oder ob er unabhängig vom Wochentag immer am 1.1. eines Jahres beginnt
Dann kann ich Dir dafür eine (kleine) Lösungsformel aufzeigen.

Gruß Werner
.. , - ...

Betrifft: Mit H1: Mo 1.1.2018 wäre das ...
von: lupo1
Geschrieben am: 07.10.2020 17:02:05

H6: =TEIL("FFFSSS____";REST(A2-1;10)+1;1)

Betrifft: Korrektur: A2 in der Formel soll H1 heißen
von: lupo1
Geschrieben am: 07.10.2020 17:03:05



Betrifft: AW: Zelle färben wenn / Arbeitstage eintrage
von: JensBrest
Geschrieben am: 07.10.2020 21:51:06

@Matthias, die Samstage sind markiert weil ich den Monat noch nicht bearbeitet habe und die Tage waren noch von jemanden von Hand markiert. Deswegen sind die Samstage rot.

Ich werde deine Formel testen und dann bescheid geben. Vielen dank :)

Zu der Formel für den Turnus: Ich habe die falschen Blätter hochgeladen. Die Arbeitstage werden in einem anderen Blatt eingetragen, dass ich unten nochmals angehangen haben. Dort wird der Turnus eingetragen und dann zu den Blättern übernommen.

Ich habe hier das Jahr 2021 gewählt.
Am 01.01.2021 habe ich meine zweite Frühschicht.

@lupo1. Deine Formel funktioniert super. Sogar ohne das du wusstest wie ich arbeite. Erstaunlich.
Beachtet die Formel auch Schaltjahr?
=TEIL("FFFSSS____";REST(A2-1;10)+1;1

 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
2 Datum01020304050607080910111213141516171819202122232425262728293031
3JANUARWoch.-TagMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFr
4 Turnus                               
501.01.2020TU - Gruppe                               
6 Datum0102030405060708091011121314151617181920212223242526272829  
7FEBRUARWoch.-TagSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSa  
8 Turnus                               
901.02.2020TU - Gruppe                               
10 Datum01020304050607080910111213141516171819202122232425262728293031
11MärzWoch.-TagSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDi
12 Turnus                               
1301.03.2020TU - Gruppe                               
14 Datum010203040506070809101112131415161718192021222324252627282930 
15APRILWoch.-TagMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDo 
16 Turnus                               
1701.04.2020TU - Gruppe                               
18 Datum01020304050607080910111213141516171819202122232425262728293031
19MAIWoch.-TagFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSo
20 Turnus                               
2101.05.2020TU - Gruppe                               
22 Datum010203040506070809101112131415161718192021222324252627282930 
23JUNIWoch.-TagMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDi 
24 Turnus                               
2501.06.2020TU - Gruppe                               
26 Datum01020304050607080910111213141516171819202122232425262728293031
27JULIWoch.-TagMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFr
28 Turnus                               
2901.07.2020TU - Gruppe                               
30 Datum01020304050607080910111213141516171819202122232425262728293031
31AUGUSTWoch.-TagSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMo
32 Turnus                               
3301.08.2020TU - Gruppe                               
34 Datum010203040506070809101112131415161718192021222324252627282930 
35SEPTEMBERWoch.-TagDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMi 
36 Turnus                               
3701.09.2020TU - Gruppe                               
38 Datum01020304050607080910111213141516171819202122232425262728293031
39OKTOBERWoch.-TagDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSa
40 Turnus                               
4101.10.2020TU - Gruppe                               
42 Datum010203040506070809101112131415161718192021222324252627282930 
43NOVEMBERWoch.-TagSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMo 
44 Turnus                               
4501.11.2020TU - Gruppe                               
46 Datum01020304050607080910111213141516171819202122232425262728293031
47DEZEMBERWoch.-TagDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDo
48 Turnus                               
4901.12.2020TU - Gruppe                               


Betrifft: AW: Zelle färben wenn / Arbeitstage eintrage
von: JensBrest
Geschrieben am: 07.10.2020 22:07:46

Das Schaltjahr wird so berechnet (AE6):
 ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG
6 Datum0102030405060708091011121314151617181920212223242526272829  
7FEBRUARWoch.-TagDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDoFrSaSoMoDiMiDo  
8 Turnus                               
901.02.2024TU - Gruppe                               

ZelleFormel
C6=A9
D6=C6+1
E6=D6+1
F6=E6+1
G6=F6+1
H6=G6+1
I6=H6+1
J6=I6+1
K6=J6+1
L6=K6+1
M6=L6+1
N6=M6+1
O6=N6+1
P6=O6+1
Q6=P6+1
R6=Q6+1
S6=R6+1
T6=S6+1
U6=T6+1
V6=U6+1
W6=V6+1
X6=W6+1
Y6=X6+1
Z6=Y6+1
AA6=Z6+1
AB6=AA6+1
AC6=AB6+1
AD6=C6+27
AE6=IF(MONTH(AD6+1)=MONTH(C6),AD6+1,"")
C7=TEXT(C6,"TTT")
D7=TEXT(D6,"TTT")
E7=TEXT(E6,"TTT")
F7=TEXT(F6,"TTT")
G7=TEXT(G6,"TTT")
H7=TEXT(H6,"TTT")
I7=TEXT(I6,"TTT")
J7=TEXT(J6,"TTT")
K7=TEXT(K6,"TTT")
L7=TEXT(L6,"TTT")
M7=TEXT(M6,"TTT")
N7=TEXT(N6,"TTT")
O7=TEXT(O6,"TTT")
P7=TEXT(P6,"TTT")
Q7=TEXT(Q6,"TTT")
R7=TEXT(R6,"TTT")
S7=TEXT(S6,"TTT")
T7=TEXT(T6,"TTT")
U7=TEXT(U6,"TTT")
V7=TEXT(V6,"TTT")
W7=TEXT(W6,"TTT")
X7=TEXT(X6,"TTT")
Y7=TEXT(Y6,"TTT")
Z7=TEXT(Z6,"TTT")
AA7=TEXT(AA6,"TTT")
AB7=TEXT(AB6,"TTT")
AC7=TEXT(AC6,"TTT")
AD7=TEXT(AD6,"TTT")
AE7=TEXT(AE6,"TTT")
A9=(DATE($AI$1,2,1))


Betrifft: AW: Zelle färben wenn / Arbeitstage eintrage
von: JensBrest
Geschrieben am: 12.10.2020 05:50:25

Vielen dank für eure Hilfe. Funktioniert super.

Wie muss ich denn die Formel ändern, wenn es passieren sollte, dass ich anstatt 3 Früh und 3 Spät
2 Früh, 2 Spät und 2 Nacht habe?

lg

Betrifft: AW: verschiedene Formelvarainaten ...
von: neopa C
Geschrieben am: 12.10.2020 09:01:21

Hallo Jens,

... wenn Du nur ein Zeichen für die Schichtkennung vergeben willst, ist es am einfachsten den Formelvorschlag von lupo entsprechend leicht anzupassen. Wobei Du das von mir nachstehend ergänzte GLÄTTEN() natürlich auch einfach wieder weglassen kannst, wenn Du nicht unbedingt ="" für Freischicht ermitteln willst.

da so: =GLÄTTEN(TEIL("FFSSNN ";REST(H1-$A$2;10)+1;1))

Wenn zwei Zeichen je Schicht vergeben werden sollen, dann z.B. so:
=GLÄTTEN(TEIL("FRFRSPSPNANA ";REST(H1-$A$2;10)*2+1;2))

Die Anpassung meines Formelvorschlages ist da ein wenig schwieriger:

Für ein Zeichen als Schichtkennzeichen so:
=WAHL(KÜRZEN((REST(H1-$A$2;10))/2;)+1;"F";"S";"N";"";"")

Dafür ist aber die Anpassung auf verschiedene Zeichenlängen der Schichtkennzeichen wiederum einfacher. Da z.B. so: =WAHL(KÜRZEN((REST(H1-$A$2;10))/2;)+1;"FR";"SP";"N";"";"")

Für alle Formeln hab ich die Zelle A2 (oder eine andere) vorgesehen, damit Du dort über die Eingabe von "" oder 1 ... 10 den Start des Turnus leicht regeln kannst ohne die Formeln zu ändern.

Gruß Werner
.. , - ...

Betrifft: AW: verschiedene Formelvarainaten ...
von: JensBrest
Geschrieben am: 13.10.2020 07:25:02

Wenn ich deine Formel =GLÄTTEN(TEIL("FFSSNN ";REST(H1-$A$2;10)+1;1)) benutze muss ich den Wert 6 eingeben in $A$2, dann passt es. Ich verstehe überhaupt nicht wie das funktioniert aber ich danke für die wunderbare Hilfe.

Betrifft: AW: vhierzu ergänzt ...
von: neopa C
Geschrieben am: 13.10.2020 08:37:19

Hallo Jens,

... in den Formeln mit TEIL() sind nach den Kennzeichen jeweils 4 (!) Leerzeichen notwendig, das geht leider aus meinen dargestellten Formeln nicht so hervor.

Gruß Werner
.. , - ...

Betrifft: Schaltj.Frage irrelev., da nur von DatumSNr. abh.
von: lupo1
Geschrieben am: 08.10.2020 00:09:31



Betrifft: AW: hierzu der Vollständigkeit halber ...
von: neopa C
Geschrieben am: 08.10.2020 07:54:27

Hallo Jens,

... Du hattest nicht nur ein falsches Kalenderblatt eingetragen.

Deine jetzige Angabe: Am 01.01.2021 habe ich meine zweite Frühschicht.
passt auch nicht damit zusammen, dass Dein Turnus am 1.1.18 begonnen hat. Deshalb hatte ich ja auch entsprechend nachgefragt. Die Formel von lupo lässt sich auch davor leicht anpassen.

Ich hatte und habe jedoch dafür eine etwas andere Formel, die etwas länger ist, die aber Deine Bezeichnungsvorgaben auch vollständig wiedergibt.

In H6: =WAHL(KÜRZEN((REST(H1-6;10))/3;)+1;"FR";"SP";"";"")

und nach rechts kopieren.

Gruß Werner
.. , - ...

Betrifft: Schichtperioden sollten aber ...
von: lupo1
Geschrieben am: 08.10.2020 08:32:24

a) unmittelbar einsehbar und
b) leicht anpassbar

sein. Daher ist das mit dem TEIL schon richtig so. Wer Klicki-Bunti-Mehrzeichen-Einträge braucht, tut das dann im Nachgang mit WECHSELN oder SVERWEIS.

Die WAHL-Formel ist mathematisch reizvoll, aber das müsste bei einer Änderung dann mühsam angpasst werden, wenn es nicht gleich unmöglich wird.