Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1828to1832
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1828to1832
1828to1832
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

JahresDienstplan automatisch

JahresDienstplan automatisch
18.05.2021 15:35:06
Michael
Hallo ich bins wieder :-))
Hab keine Ahnung und nach verschiedenen Versuche mit Unterstützung von YouTube Videos auch keinen Plan mehr,
kann mir bitte einer sagen, wie ich die Feiertage in meinen Dienstplan bringe?
Die Feiertage sollen gelb markiert werden und mit den gleichen Stunden wie die Sonntage gerechnet werden.
Hoffentlich kann mir wer helfen.
https://www.herber.de/bbs/user/146258.xlsx
MFG Michael

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: JahresDienstplan automatisch
18.05.2021 16:15:20
Michael
Ich kann deinen Link leider nicht runterladen
AW: JahresDienstplan automatisch
18.05.2021 16:46:51
Hajo_Zi

Frei
 ABC
1Jahr2021253
2 Feiertag im
Bundesland		Text der geschrieben
wird
301.01.202101.01.2021Neujahr
406.01.202106.01.2021hl. 3 Könige
515.02.2021 Rosenmontag
602.04.202102.04.2021Karfreitag
703.04.2021 Ostersamstag
804.04.202104.04.2021Ostersonntag
905.04.202105.04.2021Ostermontag
1001.05.202101.05.2021Maifeiertag
1113.05.202113.05.2021Christi Himmelfahrt
1209.05.2021 Muttertag Formel von M.I.Nitraum
1322.05.2021 Pfingstsamstag
1423.05.202123.05.2021Pfingstsonntag
1524.05.202124.05.2021Pfingstmontag
1603.06.202103.06.2021Fronleichnam Bayern
1715.08.202115.08.2021Maria Himmelfahrt
1803.10.202103.10.2021Nationalfeiertag
1903.10.2021 Reformationstag laut Hinweis im Forum gibt es noch unterschiede in den Konfesion (evangelich / katolisch)
2031.10.2021 Reformationstag
2101.11.202101.11.2021Allerheiligen
2214.11.2021 Volkstrauertag
2317.11.2021 Buss- und Bettag
2421.11.2021 Totensonntag/Ewigkeitssontag
2528.11.2021 1. Advent
2605.12.2021 2. Advent
2712.12.2021 3. Advent
2819.12.2021 4. Advent
2924.12.2021 hl. Abend
3025.12.202125.12.20211. Weihnachtstag
3126.12.202126.12.20212. Weihnachtstag
3231.12.2021 Silvester
Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich N/A
C1=NETTOARBEITSTAGE(A3;A32;B3:B32)  
A3=DATWERT("01.01."&B1)  
A4=DATWERT("06.01."&B1)  
A5=A8-48  
A6=A8-2  
A7=A8-1  
A8=DM((TAG(MINUTE(B1/38)/2+55) & ".4." & B1)/7;)*7-WENN(JAHR(1)=1904;5;6)  
A9=A8+1  
A10=DATWERT("01.05."&B1)  
A11=A8+39  
A12=DATUM(B1;5;1)+15-WOCHENTAG(DATUM(B1;5;1))  
A13=A8+48  
A14=A8+49  
A15=A8+50  
A16=A8+60  
A17=DATUM(B1;8;15)  
A18=DATWERT("03.10."&B1)  
A19=DATUM(B1;10;1)+7-WOCHENTAG(DATUM(B1;10;1);2)  
A20=DATWERT("31.10."&B1)  
A21=DATWERT("01.11."&B1)  
A22=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-35  
A23=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-32  
A24=DATUM(B1;12;25)-WOCHENTAG(DATUM(B1;12;25);2)-28  
A25=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-21  
A26=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-14  
A27=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)-7  
A28=DATUM($B$1;12;25)-WOCHENTAG(DATUM($B$1;12;25);2)  
A29=DATWERT("24.12."&B1)  
A30=DATWERT("25.12."&B1)  
A31=DATWERT("26.12."&B1)  
B3:B4,B6,B8:B11,B14:B18,B21,B30:B31=A3  
A32=DATWERT("31.12."&B1)  
C3:C32=D3  

Zahlenformate
Zelle Format Inhalt
A3:B3 'TT.MM.JJJJ  44197
A4:B4 'TT.MM.JJJJ  44202
A5 'TT.MM.JJJJ  44242
A6:B6 'TT.MM.JJJJ  44288
A7 'TT.MM.JJJJ  44289
A8:B8 'TT.MM.JJJJ  44290
A9:B9 'TT.MM.JJJJ  44291
A10:B10 'TT.MM.JJJJ  44317
A11:B11 'TT.MM.JJJJ  44329
A12 'TT.MM.JJJJ  44325
A13 'TT.MM.JJJJ  44338
A14:B14 'TT.MM.JJJJ  44339
A15:B15 'TT.MM.JJJJ  44340
A16:B16 'TT.MM.JJJJ  44350
A17:B17 'TT.MM.JJJJ  44423
A18:A19, B18 'TT.MM.JJJJ  44472
A20 'TT.MM.JJJJ  44500
A21:B21 'TT.MM.JJJJ  44501
A22 'TT.MM.JJJJ  44514
A23 'TT.MM.JJJJ  44517
A24 'TT.MM.JJJJ  44521
A25 'TT.MM.JJJJ  44528
A26 'TT.MM.JJJJ  44535
A27 'TT.MM.JJJJ  44542
A28 'TT.MM.JJJJ  44549
A29 'TT.MM.JJJJ  44554
A30:B30 'TT.MM.JJJJ  44555
A31:B31 'TT.MM.JJJJ  44556
A32 'TT.MM.JJJJ  44561
B5, B7, B12:B13, B19:B20, B22:B29, B32 'TT.MM.JJJJ 
C3 'TT.MM.JJJJ  Neujahr
C4 'TT.MM.JJJJ  hl. 3 Könige
C5 'TT.MM.JJJJ  Rosenmontag
C6 'TT.MM.JJJJ  Karfreitag
C7 'TT.MM.JJJJ  Ostersamstag
C8 'TT.MM.JJJJ  Ostersonntag
C9 'TT.MM.JJJJ  Ostermontag
C10 'TT.MM.JJJJ  Maifeiertag
C11 'TT.MM.JJJJ  Christi Himmelfahrt
C12 'TT.MM.JJJJ  Muttertag Formel von M.I.Nitraum
C13 'TT.MM.JJJJ  Pfingstsamstag
C14 'TT.MM.JJJJ  Pfingstsonntag
C15 'TT.MM.JJJJ  Pfingstmontag
C16 'TT.MM.JJJJ  Fronleichnam Bayern
C17 'TT.MM.JJJJ  Maria Himmelfahrt
C18 'TT.MM.JJJJ  Nationalfeiertag
C19 'TT.MM.JJJJ  Reformationstag laut Hinweis im Forum gibt es noch unterschiede in den Konfesion (evangelich / katolisch)
C20 'TT.MM.JJJJ  Reformationstag
C21 'TT.MM.JJJJ  Allerheiligen
C22 'TT.MM.JJJJ  Volkstrauertag
C23 'TT.MM.JJJJ  Buss- und Bettag
C24 'TT.MM.JJJJ  Totensonntag/Ewigkeitssontag
C25 'TT.MM.JJJJ  1. Advent
C26 'TT.MM.JJJJ  2. Advent
C27 'TT.MM.JJJJ  3. Advent
C28 'TT.MM.JJJJ  4. Advent
C29 'TT.MM.JJJJ  hl. Abend
C30 'TT.MM.JJJJ  1. Weihnachtstag
C31 'TT.MM.JJJJ  2. Weihnachtstag
C32 'TT.MM.JJJJ  Silvester
Zellen mit Format Standard werden nicht dargestellt

Füllfarben und Muster  
Zelle Rot Grün Blau Color Muster Farbe
B2:B32 20425520413434828   
Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate  
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
A1, C1     Fett         
Zellen mit Schriftformatierung automatisch werden nicht dargestellt


Gruß Hajo
Anzeige
AW: JahresDienstplan automatisch
18.05.2021 15:51:12
SF
Hola,
deine "Feiertage" sind allesamt TEXT. Trenne Bezeichnung und Datumswerte in eigene Zellen auf.
Den Bereich "Feiertage" den du im Sverweis ansprichst gibt es nicht. Im Sverweis ist auch die Fixierung falsch.

=SVERWEIS($A6;Feiertage;1;0)
Wo steht denn wie Sonntagsstunden gerechnet werden?
Gruß,
steve1da
AW: JahresDienstplan automatisch
18.05.2021 15:55:06
Michael
hy, ahso danke, die Sonntagsstunden werden mit 08:00-19.30 Uhr und mit 11,5 h gerechnet. die feiertage dann in ZAHL statt Text umwandeln?
AW: der Zusammenhang wäre nachvollziehbarer ...
18.05.2021 16:01:29
neopa
Hallo nochmal,
... wenn Du auf den vorhergehenden thread hier: https://www.herber.de/forum/archiv/1828to1832/t1831332.htm verwiesen hättest bzw. die Zusatzfrage gleich dort gestellt hättest.
Gruß Werner
.. , - ...
Anzeige
AW: der Zusammenhang wäre nachvollziehbarer ...
18.05.2021 16:22:12
Michael
Kannst du mir bitte sagen wie ich den Code ändern soll, das er auf die Feiertage auch geht? und nicht nur auf die Sonntage?
Die Arbeitszeit und die Stunden.
AW: ist dann möglich ...
18.05.2021 16:25:03
neopa
Hallo Michael,
... wenn Du zunächst Deine Feiertagsliste entsprechend so trennst, wie von mir beschrieben.
Gruß Werner
.. , - ...
AW: wie bereits geschrieben mit ZÄHLENWENN() ...
18.05.2021 16:34:45
neopa
Hallo Michael,
.. . in F6: =WENNFEHLER(WENN((REST(A6;7)=1)+ZÄHLENWENN(feiertage;A6);11,5;INDEX({" ";" ";" ";12;12;12};REST(C6;6)+1));"")
und Formel nach unten kopieren.
Und ZÄHLENWENN() hätte ich auch entsprechend für die bed. Formatierung eingesetzt.
Gruß Werner
.. , - ...
Anzeige
AW: wie bereits geschrieben mit ZÄHLENWENN() ...
18.05.2021 16:41:04
Michael
Super hat geklappt, :-) und bei den Stunden, sodass er wie an den Sonntagen von 08.00-19.30 schreibt?
AW: Deine Zusatzfrage ...
24.05.2021 09:33:06
neopa
Hallo Michael,
... hab erst jetzt festgestellt, dass dies meinerseits noch unbeantwortet war.
Die Lösung dafür ist natürlich einfach analog zu der in F6. Also in E6:
=WENNFEHLER(WENN((REST(A6;7)=1)+ZÄHLENWENN(feiertage;A6);WECHSELN(INDEX({"Frei";"Frei"; "Frei";"07.30-19.30";"07.30-19.30";"07.30-19.30"};REST(C6;6)+1);"7.3";"8:0");INDEX({"Frei";"Frei"; "Frei";"07.30-19.30";"07.30-19.30";"07.30-19.30"};REST(C6;6)+1));"")
und nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: die Feiertage erfasse als Datumswerte ...
18.05.2021 15:52:57
neopa
Hallo Michael,
... und nicht komplett mit dessen Bezeichnung in einer Zelle sondern dies getrennt in einer Nachbarzelle.
Für die bedingte Formatierung und die Ermittlung der Sollzeiten kannst Du diese dann mit ZÄHLENWENN() auswerten.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige