Dynamischer Excel Kalender mit Feiertage Frühschicht Spätschicht Nachtschicht
Auf eine Anfrage von PAUL 😉 habe ich jetzt den normalen DYNAMISCHEN KALENDER etwas erweitert.
Es ging hierbei zum Ersten um die Kalenderwochen, wobei man sich hier auch erst einmal schlau machen musste wann eine Woche eigentlich im Jahr anfängt. Hier muss man nämlich aufpassen, denn wenn der 1. Januar z.B. ein Samstag ist, dann wäre es nicht schon die erste Woche. Deshalb kam die Formel ISOKALENDERWOCHE zum Einsatz. Der Zweite Punkt waren das Schichtsystem also habe ich zusätzlich zwei Varianten der Tabelle erstellt. Einmal für diejenigen die nur Früh und Spätschicht machen und dann noch eine für 3 Schichten. Also Frühschicht, Spätschicht und Nachtschicht. Somit könnt Ihr den Kalender entsprechend ganz leicht Eurem Schichtplan anpassen.
Wichtig: Die Reihenfolge der Schichten könnt Ihr auf der rechten Seite der Tabelle Eurem Schichtplan entsprechend anpassen. Wer z.B. mit Spätschicht oder Nachtschicht anfängt ändert einfach die Zellen entsprechend. Genauso können natürlich die Texte sowieso geändert werden wie Ihr möchtet.
Ihr könnt Euch die Tabelle natürlich noch etwas aufhübschen aber die Funktionen sind soweit drin.
Achtung: Die Funktion ISOKALENDERWOCHE steht erst ab Excel 2013 zur Verfügung.
Für den Kalender mit Früh und Spätschicht könnt Ihr ersatzweise dann folgende Formeln eintragen:
Für die Wochen (AB Zelle A4 und von da aus runter kopieren):
=WENNFEHLER(KALENDERWOCHE(C4;2)-WENN(WOCHENTAG(DATUM($B$1;1;1);2)<5;0;1);"")
Für die Schichten (AB Zelle D4 und von da aus runter kopieren):
=WENNFEHLER(WENN(REST(KALENDERWOCHE(C4;2)-WENN(WOCHENTAG(DATUM($B$1;1;1);2)<5;0;1);2)=0;$BA$4;$BA$5);"")
Dynamischer Excel Kalender für Feiertage und 2 Schichten
(FRÜHSCHICHT und SPÄTSCHICHT)
Download
Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉
Dynamischer Excel Kalender für Feiertage und 3 Schichten
(FRÜHSCHICHT, SPÄTSCHICHT, NACHTSCHICHT)
Download
Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉
Dynamischer Excel Kalender für Feiertage und mehrere Schichten
(INDIVIDUELL im 7 Tage Rhythmus)
Hier könnt Ihr Euren Kalender individuell anpassen. Auf der rechten Seite des Kalenders müsst Ihr nur die Einträge der Reihenfolge nach setzen.
Download
Euch gefällt die Datei und Ihr wollt Euch unbedingt bedanken?
Hier ist meine AMAZON – Wunschliste [HIER KLICKEN] 😉
Viel Spaß damit
Chris
19 Comments
Hallo Chris
Vielen Dank für deine vielen Tutorials. Ich versuche gerade, mit meinen beschränkten Kenntnissen, den Früh Mittag Nacht Kalender auf 6 Schichten (Nacht Nacht Mittag Früh Mittag Früh) anzupassen (mit Libre calc) aber da renn ich irgendwie gegen ne Wand. Hast du eine Idee?
Cu Dirk
Hallo Dirk,
das ist schon etwas komplizierter. Ich habe mich gerade mal da ran gesetzt und versuche Dir mal zu schildern wie Du vorgehen könntest.
Erstelle dir im rechten Bereich dort wo die 3 Schichten stehen eine kleine Matrix. Da steht links in der Spalte einfach untereinander nur durchnummeriert 0, 1, 2, 3, 4, 5, 6, 7 und rechts daneben die entsprechenden Schichten.
Also Nacht, Nacht, Mittag, Früh, Mittag, Früh und ich habe jetzt noch 2 mal frei genommen. Falls das nicht so sein sollte musst Du die Formel entsprechend anpassen.
Anschließend markierst Du die Zellen und gibst denen einen Namen. In meinem Beispiel habe ich die einfach MehrSchichten genannt. Das ist sozusagen der Name den die Funktion SVerweis als Matrix benötigt.
Danach kopierst Du Dir folgende Formel in die erste Zelle wo die erste Schicht im Kalender steht. Bei mir ist das die Zelle D4. Aber darauf achten das bei mir die Jahreszahl des Kalenders in Zelle B1 steht. Falls bei Dir das anders sein sollte musst Du statt $B$1 natürlich die Zelle nehmen wo Deine Jahreszahl vom Kalender drin steht. Aber auch hier den absoluten Bezug nutzen, also die Dollarzeichen nicht vergessen 😉
=WENNFEHLER(SVERWEIS(REST(TAGE(C4;DATUM($B$1;1;1));8);MehrSchichten;2;0);"")
Das kannst Du Dir dann in den gesamten Kalender in die Bereiche kopieren wo vorher die Schicht drin stand aber immer bis zum unteren Ende des Kalenders (also z.B. D4 bis D34 für Januar. Für die anderen Monate natürlich ebenfalls).
Allerdings ist das jetzt noch nicht ausgiebig getestet und musst Du selbst noch mal kontrollieren. Also benutze am Besten keine Originaldatei 😉
Kannst ja mal bescheid geben ob alles funktioniert hat. War schon etwas spät und ich muss um 4 Uhr wieder raus.
Viele Grüße
Chris
Hallo Chris,
Ich habe eine Frage: Vielleicht kannst du mir dabei weiterhelfen. Ich suche eine einzige Formel wie man die Nachtschicht berechnen kann. Ich habe jetzt schon überall geschaut aber wirklich nix gefunden wie man das berechnen kann.
Kurze Erklärung. Ich habe mir 2 Tabelle angelegt
Tabelle 1 steht
Die Nachtschicht in, (Tabelle1)
Feld B14 beginn 22:00
C14 Ende 06:00
Bei der Arbeitserfassung (Tabelle 2) steht in,
Feld C8 Arbeitsbeginn
Feld E8 Arbeitsende
und in G8 brauchte man dafür die Formel für die Nachtschicht von 22:00 – 06:00 für die Stdt.
Sei mir nicht sauer deswegen auf die e-Mailadresse. Aber vielleicht erfahre man das mit der Formel dafür. Chris oder ein Video darüber wenn das nicht zu viel Arbeit wäre für dich.
Bernd
Hallo Chris
Erst mal Danke für deine hilfe.
Hab mir heute auf Mittag das mal angesehen (hatte ja zeit 😉 ) und das mal auf meinen Kalender umgestrickt. Hab gleich den neuen (nächsten kommenden schichtplan) versucht einzubinden, was mit der Anzahl der Schichten auch klappt, aber danach hätte ich jetzt am 1,1,18 N1 am 2,1,18 N2 am 3.1.18 M1…..usw.
Hier mal die von mir erstellte Formel :=WENNFEHLER(SVERWEIS(REST(TAGE(A3;DATUM($A$1;1;1));12);MehrSchichten;2;0);"")
Jahr ist in A1, der 1.1.18 ist in A3 formel ist eingefügt in B3
ich weißt jetzt nicht genau was Du meinst. Der Schichtplan fängt immer am Anfang des Jahres neu an. Wenn Du da etwas für das nächste Jahr ändern müsstest kannst Du das in der Reihenfolge auf der Rechten Seite (die Matrix) entsprechend drehen. Ansonsten bräuchtest Du einen anderen Bezugspunkt. In der Formel wird berechnet welcher Tag des Jahres ist. Deshalb beginnt die Rechnung auch immer vom Anfang des Jahres. Du kannst es eventuell noch anstatt mit den Tagen auch mit dem Datum als Zahl machen und musst dann entsprechend in der Formel Tage abziehen.
Hallo chris
Wenn ich die formel so benutze :=WENNFEHLER(SVERWEIS(REST(TAGE(A3;DATUM($A$1;1;1));12);MehrSchichten;2;0);““)
hat jeder tag im januar eine andere schicht. Bei der formel für drei schichten wird der bereich sonntag bis samstag als z.b. nachtschicht markiert. Das funktioniert bei mir nicht
Sorry für die ganzen fragen
Cu dirk
Achso. Jetzt habe ich vermutlich erst verstanden was Du für eine Schicht meinst. Ich dachte Du hast sowas wie Vollkonti – Schicht wo Du jeden Tag eine andere Schicht hast. Deshalb bin ich auch davon aus gegangen dass Du nach diesen unterschiedlichen Schichten auch Frei haben müsstest. Das sind immer Angaben von Wochen gewesen. Ich hatte mich sowieso gewundert warum Du da auch nach der Früh nochmal Mittagsschicht hast. Ich hatte nämlich vor ein paar Jahren solch eine ähnliche Schicht wo ich 2 Tage Nachtschicht hatte 2 Tage Spätschicht und 3 Tage Frühschicht.
Versuche mal folgenden Ansatz:
=WENNFEHLER(SVERWEIS(REST(WOCHEN(DATUM($B$1;1;1);C4;0);8);MehrSchichten;2;0);"")
Musst Du aber noch anpassen. Habe mich gewundert dass Du dort in der Formel eine 12 zu stehen hast. Kann sein dass der Wert kleiner sein muss, denn laut Deines Plans sollte der Wert niedriger sein.
Hallo Chris
Vielen Dank für deine Mühe. Hätte mich bischen besser ausdrücken können bei den Schichten.
Aber nun klappt es wunderbar. Ich glaub bei kontischichten würde ich entgültig zusammen brechen. Mit derzeit 2 Nacht 1 Mittag 1 Früh und dann von vorne komme ich schon nicht mehr zurecht. Ab 2018 gehts dann wieder zurück auf mindesten 3 nacht 1Mittag 1 Früh plus Überraschung daher 12 Schichten.
Nochmals vielen Dank
Hoffe das hilft auch anderen.
Cu Dirk
Super, freut mich das es funktioniert. Ich bin mir sicher dass sich auch noch andere dafür interessieren. Werde da aber trotzdem nochmal ein extra Tutorial daraus basteln und hochladen wenn es die Zeit zulässt 😉
Das mit den Kontischichten war schon eine harte Zeit und man hofft natürlich dass man davon verschont bleibt. Aber leider kann man ja nie wissen was die Zukunft noch so für einen bereit hält.
Die Formeln kann man aber jedenfalls noch weiter anpassen. Jetzt sind ja beide Varianten (wöchentliche oder tägliche Änderungen) da 😉
Hallo Chris.
Ich muss Dir noch mal auf den Pinsel gehen.
Hab das mit den Schichten jetzt auch so hinbekommen das, wenn ein Kollege den bekommt, er über Pull/Dropdownmenue direkt seine Gruppenschicht angezeigt bekommt (stolz ich bin ;-))
Was ich aber ( mal wieder ) nicht hinbekomme ist: Die Wochen (Schichten) Sonntags beginnen zu lassen.
Vieleicht hast Du dazu eine Idee
Cu
Dirk
Ich bin mir natürlich nicht sicher welche Formeln Du jetzt alle benutzt. Solltest Du aber z.B. die Formel WOCHENTAG benutzen, könntest Du dort als zweiten Paremeter eine andere Zahl nehmen. Wenn die Woche mit Montag anfängt steht dort für gewöhnlich eine 2 wenn die Woche mit Sonntag anfangen soll müsste dort eine 1 stehen oder der Parameter komplett entfallen. Kannst Du ja mal mit einer Kopie Deiner Datei ausprobieren ob das schon funktioniert.
Viele Grüße
Chris
Hallo Chris
Ich sollte mir angewöhnen bei fragen auch das problem dazu zu schreiben. Der Schichtenplan basiert auf dieser Formel:
=WENNFEHLER(SVERWEIS(REST(WOCHEN(DATUM($A$1;1;1);B5;0);12);Schichten;2;0);"")
Funktioniert auch fängt jedoch leider mit Montag statt Sonntag an. Hab das mal versucht zu ändern was mir aber nicht gelingt. Denke das es mit diesem Parameter zusammen hängt: <5;0;1 wobei ich nicht den ansatz finde wo ich diesen einfügen muss.
Viele grüße
Dirk
Hi Dirk, versuche mal nach dem Datum eine minus 1. Also DATUM($A$1;1;1)-1.
Mir ist gerade auch aufgefallen das es nicht so gut ist hier in die Kommentare vom Excel Kalender Formeln für LibreOffice Calc einzutragen. Die Funktion WOCHEN gibt es dort nämlich nicht und deshalb könnte das hier für andere ziemlich irritieren werden. Also nächsten mal unbedingt auch in den Bereich von LibreOffice Calc schreiben.
Viele Grüße
Chris
Hey erstmal danke für den Super Kalender.
Ich arbeite in einem 3 Schichten System in einer 6 Tage Woche.
Also 2x Früh 2x Spät 2x Nacht dann 4x Frei.
Das wiederholt sich immer wieder unabhängig von Feiertagen oder Wochenenden.
Würde es ganz gerne in deine Datei einbauen, steige aber da nicht richtig durch.
Hast du eine Lösung für mich?
LG und gute Rutsch Rafael
Hey Raffael, schau mal unten in die Kommentare. Dort hat jemand das gleiche Anliegen gehabt und der Lösungsansatz ist dort enthalten. Schau Dir mal die Formeln in den Kommentaren an. Die Formeln die dort stehen können so angepasst werden dass es für verschiedene Schichtkalender genutzt werden kann.
Viele Grüße und ebenfalls einen super Rutsch ins neue Jahr.
Chris
Hallo Chris
habe es mi dieser Formel =WENNFEHLER(SVERWEIS(REST(WOCHEN(DATUM($B$1;1;1)-1;C4;0);10);MehrSchichten;2;0);““) und der Matrix versucht
aber leider kommt bei mir nur eine Fehlermeldung.
Da ich mich nicht so gut auskenne mit dieser Formel kann ich mir leider kein Reim daraus machen wo der Fehler liegt.
LG Rafael
Hi Rafael,
schau mal hier nochmal im unteren Bereich. Ich habe jetzt eine Datei zum Download bereit gestellt. Hier kannst Du im rechten Bereich des Kalenders bis zu 7 verschiedene Schichten eintragen. Diese Schichten werden dann im Kalender entsprechend angezeigt.
https://toptorials.com/dynamischer-excel-kalender-mit-feiertage-fruehschicht-spaetschicht-nachtschicht-download/
Hallo Chris,
ich würde gerne den Kalender als Dienstplan für unseren Verein nutzen. Dazu müsste ich alle Mannschaften hinterlegen aktuell sind es 7 die dann immer eine ganze Woche Dienst haben. Ich habe es jetzt mehrmals versucht aber bekomme es nicht hin, die Mannschaften wechseln Täglich.
Das ist die Formel die in der Spalte D4 eingefügt ist
=WENNFEHLER(SVERWEIS(REST(TAGE(C4;DATUM($B$1;1;1));7);MehrSchichten;2;0);"")
Ich danke dir schon vorab für deine Hilfe.
Gruß
Rafael
Hallo Chris, erstmal sage ich Danke für deine ganzen Videos. Anhand derer konnte ich schon einige Dinge in Calc bearbeiten und immer besser verschiedene Dateien erstellen.
Ich habe eine Frage zu einem dynamischen Kalender (den ich auch mit deiner Hilfe erstellen konnte).
Ich würde aus so einem Kalender gerne einen Dienstplan erstellen, der wie folgt aussehen soll:
Es gibt 3 Touren (A,B,C) und wir arbeiten immer 3xFrühdienst,4xSpätdienst,3xNachtdienst,4Frühdienst,3xSpätdienst,4xNachtdienst,3xFrühdienst…
Wenn Tour A Frühdienst hat dann hat Tour B Spät und Tour C Nacht.
Hast du eine Formel, die mir automatisch diese Schichtabfolge für die jeweilige Tour rauswirft?
Ich habe es angelehnt an die Formel =WENN(MONAT(B6+1)=MONAT(B$6);B6+1;"") versucht, aber nichts hat so richtig geklappt..
Lieben Gruß Chrizi