Formel in Excel ohne Feiertage?
Hallo zusammen,
Ich habe eine Formel gefunden womit Excel das Datum des nächsten Freitags anzeigt:
=D1+(wochentag(D1;2)7+(5-wochentag(D1;2)
In D1 steht zum Beispiel 08.07.2019 drin und in die Zelle wo die Formel drin steht zeigt Excel dann den 12.07.2019 an.
Es klappt auch alles. Es gibt nur einen Haken: ich würde gerne, dass Excel mir wenn der Freitag ein Feiertag ist den Donnerstag anzeigt.
Wie funktioniert das?
Wie kann ich in diese Formel noch die Feiertage ausschließen und dann den Tag davor einblenden lassen?
Liebe Grüße und Danke im Voraus.
8 Antworten
Du brauchst eine Liste der Feiertage, die in Deinem Land gelten.
Die schreibst Du z.B. in F1:F100 (für mehrere Jahre*).
Dann diese Formel für den nächsten Freitag oder Donnerstag:
=WENN(ZÄHLENWENN($F$1:$F$30;D1+7-REST(WOCHENTAG(D1+1;17);7))>0;D1+7-REST(WOCHENTAG(D1+1;17);7)-1;D1+7-REST(WOCHENTAG(D1+1;17);7))
Kann sein, dass es noch eleganter geht. aber nicht mehr um diese Uhrzeit 😉.
*Man könnte die Liste für alle Jahre auch über die "Osterformel" generieren, aber das wird mir im Moment zu kompliziert.
P.S.: Ich denke mal, dass Freitag UND Donnerstag ein Feiertag ist, kann - zumindest in Deutschland - nicht vorkommen, oder?
Bevor ich anfange zu "fummeln":
Gilt da auch das Feiertagsproblem? Und wenn ja, dann statt des Montags den Sonntag oder den Freitag?
P.S.: Ich werde allerdings wahrscheinlich frühestens morgen dazu kommen das zu bearbeiten.
@oubyi Das gilt leider auch als Feiertagsproblem. Vielen Dank fürs Kümmern und morgen reicht vollkommen. LG
Bleibt die Frage offen, ob Sonntag statt Montag oder Freitag statt Montag.
Teste mal ausführlich:Nächster Montag (oder Sonntag):
=WENN(ZÄHLENWENN($F$1:$F$30;D1+7-REST(WOCHENTAG(D1+1;13);7))>0;D1+7-REST(WOCHENTAG(D1+1;13);7)-1;D1+7-REST(WOCHENTAG(D1+1;13);7))
Nächster Montag (oder Freitag):
=WENN(ZÄHLENWENN($F$1:$F$30;D1+7-REST(WOCHENTAG(D1+1;13);7))>0;D1+7-REST(WOCHENTAG(D1+1;13);7)-3;D1+7-REST(WOCHENTAG(D1+1;13);7))
Übernächster Freitag (oder Donnerstag):
=WENN(ZÄHLENWENN($F$1:$F$30;D1+14-REST(WOCHENTAG(D1+1;17);7))>0;D1+14-REST(WOCHENTAG(D1+1;17);7)-1;D1+14-REST(WOCHENTAG(D1+1;17);7))
Moin,
warum so Kompliziert ?
Das Excel Datumsformat ist in der Lage den Wochentag anzuzeigen. Benutzerdefiniertes Format: z.B. "TTT TT.MM.JJJJ" (3x Buchstaben=Abkürzung; 4x Buchstaben=Ausgeschrieben)
Die Feiertag musst du wissen. Die Festen kennst du, die Veränderlichen rechnest du über die Osterformel in Wikipedia aus. Man braucht ein bisschen Hirnschmalz, hier hab ich es selbst gemacht in meinem Schichtkalender.
https://www.ms-office-forum.net/forum/showthread.php?t=348017
Da findest du in der Hilfstabelle auch die Umsetzung der Osterformel für Excel.
Mithilfe von Formaten; Bedingter Formatierung; und Sverweis zeige ich die Feiertage jeweils an. Ich zeige nur eine 1 an. In der Zelle steht aber 01.01.2017 => Neujahr=>Feiertag.
Excel rechnet in ganzen Tagen. 01.01.2019+1 = 02.01.2019 Zeiten sind Bruchteile von Tage. (Excel rechnet automatisch um) Es kennt auch die korrekten Schaltjahre. Nur vor 1900 muss man aufpassen. ...
Grüße
Das ginge z.B. so, wenn ich Dich richtig verstanden habe
(die Spalten E;F;H;J und K sind/waren nur zur Kontrolle und können weggelassen werden)
=WENN(WOCHENTAG(WENN(ISTFEHLER(SVERWEIS(D2+7;I$2:I$20;1;FALSCH));D2+7;D2+6))=5;WENN(ISTFEHLER(SVERWEIS(D2+7;I$2:I$20;1;FALSCH));D2+8;D2+6);WENN(ISTFEHLER(SVERWEIS(D2+7;I$2:I$20;1;FALSCH));D2+7;D2+6))
Anmerkung:
Es wenig Feiertage, die auf einen Freitag fallen.
Wenn du nur bedenkst, das es schon in verschiedenen Bundesländern unterschiedliche Feiertage gibt und in verschiedenen Ländern noch viel mehr, dann lässt sich abschätzen, dass so etwas in EXCEL wohl kaum unter akzeptablem Aufwand darstellbar ist. Sollte mich jemand eines Besseren belehren, lerne ich gerne dazu.
Es gibt sie aber. Und die aufwändigste Formel nützt wenig, wenn sie Fehler ausgibt.
Im Feiertagskalender werden einfach die unzutreffenden entfernt. Fertig, dann gibt es keine Fehler
Es gibt meines Wissens 7 oder 8 Feiertage, die nur in bestimmten Bundesländern gelten.
Aber soll mir recht sein. Wenn man meint, ein Problem unbedingt mit ECXEL lösen zu müssen, dann kann man das gerne tun.
Nun, es geht um die Frage und eine mögliche Lösung dazu.
nach dem Warum frage ich nicht, das wird/wurde anderswo entschieden.
Es könnte Gründe dafür geben.
Diese Formel habe ich nicht verstanden.
=D1+(wochentag(D1;2)7+(5-wochentag(D1;2)
Wenn Du in D1 das Datum des 1. Freitags des Jahres eingibst, dann kannst Du in D2 folgendes eingeben. +D1+7 und herunterkopieren. Fertig
Dann hast Du jeden folgenden Freitag.
Hallo. Erstmal vielen vielen Dank für die Formel. Sie klappt. 😊
Kannst du mir nochmal helfen ich brauche dazu auch noch den nächsten Montag und den übernächsten Freitag.....