Dass Excel gut mit Uhrzeiten und Datum rechnen kann, ist hinlänglich bekannt. Intern ist ein Datum einfach eine fortlaufende Nummer.
Da die Excel-Zeitrechnung mit dem 01.01.1900 beginnt, stellt dieses Datum den Wert 1 dar. Und der 06.09.2013 wäre damit der Wert 41523.
Excel bringt eine ganze Menge an Funktionen zur Berechnung eines Datums mit. So lässt sich z.B. mit der =HEUTE()-Funktion ganz einfach das aktuelle Datum einfügen.
Aber es gibt eine Reihe an Datumsfunktionen, die vielen wahrscheinlich nicht so geläufig sind. Und was man nicht weiß … bringt einen auch nicht weiter. Daher stelle ich in diesem Artikel ein paar weniger bekannte Funktionen vor, die sehr nützlich sein können.
Und so geht’s:
Arbeitstage
Du willst ein zukünftiges Datum berechnen, aber es sollen dabei nur Arbeitstage berücksichtigt werden? Dafür gibt es die Excel-Funktion =ARBEITSTAG().
Zwei Parameter werden minimal benötigt: Das Ausgangsdatum und die Anzahl der Arbeitstage, die Du dazu addieren möchtest:
Bild: Arbeitstage
Wie man am Beispiel sieht, wird das Wochenende vom 10. und 11.08. nicht mitgezählt und somit der 16.08. als korrektes Datum zurückgeliefert. Wie sieht es aber mit Feiertagen aus?
Excel berücksichtigt bei der Funktion ARBEITSTAG() standardmäßig nur Samstage und Sonntage. Es lassen sich aber über einen dritten Parameter weitere Tage ausschließen. In Bayern ist z.B. der 15.08. ein Feiertag. Daher möchte ich ihn in meiner Berechnung nicht berücksichtigen:
Bild: Arbeitstage
Da im Anschluß wieder ein Wochenende kommt, wir somit der 19.08. als korrektes Datum ermittelt:
Bild: August
Selbstverständlich kann auch eine Liste an Tagen übergeben werden, die nicht als Arbeitstage zu werten sind:
Bild: Arbeitstage
Bild: Dezember
Somit wird der 02. Januar als korrektes Datum ermittelt.
Einziger Wermutstropfen: Halbe Arbeitstage können leider nicht berücksichtigt werden.
Nettoarbeitstage
Eng verwandt damit ist die Funktion =NETTOARBEITSTAGE().
Gib ein Anfangs- und ein Enddatum an und Excel sagt Dir, wieviele Arbeitstage dazwischen liegen.
Bild: Nettoarbeitstage
Auch hier gilt wieder, dass nur Samstage und Sonntage ausgeklammert werden und zusätzliche Feiertage als weiterer Parameter übergeben werden können:
Bild: Nettoarbeitstage
Kalenderwoche
Eine ebenfalls sehr praktische, aber größtenteils unbekannte Funktion: =KALENDERWOCHE().
Sie tut genau, was der Name vermuten lässt: Mit ihr ermittelt man, in welcher Kalenderwoche ein angegebenes Datum liegt. Als optionaler Parameter kann noch ein Zahl_Typ eingeben werden. Damit steuert man, ab wann eine Woche beginnen soll: 1 = Sonntag, 2 = Montag.
Bild: Zahl_Typ = 1
Bild: Zahl_Typ = 2
Somit kommen also unter Umständen unterschiedliche Kalenderwoche dabei heraus. Wird der Zahl_Typ nicht mit angegeben, rechnet Excel standardmäßig mit „1“.
Wochentag
Vielleicht möchtest Du ermitteln, auf den wievielten Wochentag ein bestimmtes Datum fällt. Dazu gibt es in Excel die Funktion =WOCHENTAG().
Als Parameter wird das jeweilige Datum angeben, optional ist es auch möglich, einen bestimmten Typ für den zurückgegebenen Wert festzulegen:
Bild: Wochentag
Im Beispiel oben ist der 06.09. ein Freitag. Excel gibt als laufenden Wochentag „6“ zurück. Dies liegt daran, dass als Typ „1“ angegeben wurde. Über den Typ legt man fest, wie die Tage gezählt werden.
Typ 1 (oder nicht angegeben):
gibt den Wert 1 (für Sonntag) bis 7 (für Samstag) zurück
Typ 2:
gibt den Wert 1 (für Montag) bis 7 (für Sonntag) zurück
Typ 3:
gibt den Wert 0 (für Montag) bis 6 (für Sonntag) zurück.
Bild: Wochentag
Und somit wird der Freitag als „5“ interpretiert.
Jahr, Monat, Tag
Um aus einem gegebenen Datum nur das Jahr, den Monat oder den Tag für die Weiterverarbeitung „herauszuschneiden“, gibt es die drei gleichnamigen Funktionen: =JAHR(), =MONAT() und =TAG()
Die Anwendung ist denkbar einfach, als Parameter wird nur ein Datum übergeben:
Bild: Jahr, Monat, Tag
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo,
zum Thema der Datumsfunktionen kann ich vielleicht etwas beisteuern. Die Problematik betrifft die Bestimmung der Feiertage. Die festen Feiertage sind ja kein Problem. Die beweglichen Feiertage richten sich nach dem Ostersonntag.
Dies ist der erste Sonntag nach dem ersten Vollmond nach Frühlingsanfang.
Auch das kann mit Excel berechnet werden. Ich habe eine Formel gefunden, die diesen Tag berechnet:
=RUNDEN((TAG(MINUTE(B1/38)/2+55)&“.4.“&B1)/7;0)*7-6
In Zelle B1 steht in diesem Beispiel die Jahreszahl (z.B.: 2014)
Karfreitag, Pfingsten, Christi Himmelfahrt, Frohnleichnam und sogar Karneval kann nach diesem errechneten Ostersonntag bestimmt werden.
Wohl gemerkt, ist die Formel nicht von mir erstellt worden. Ich habe sogar mal versucht die Formel aufzudröseln, was mir aber nicht gelungen ist. Auch eine Formel für die Mondphasen habe ich nicht erstellen können. Warum in der Formel „Tag“ und „Minute“ mit einfließen, kann ich nicht nachvollziehen.
Aber die Formel funktioniert und ich erstelle damit Kalender, die Sonn- und Feiertage farbig darstellt über die bedingte Formatierung.
mit freundlichem Gruß
HP Follmann
Hallo Hans-Peter,
danke für die Formel, da wird sicherlich schon der ein oder andere Leser mal danach gesucht haben.
Ich hatte eine ähnliche Version in meiner Kalendervorlage verwendet. Auch mir erschließt sich leider der tiefere Sinn dieser Formel nicht. Aber sie funktioniert, und das ist die Hauptsache.
Schöne Grüße,
Martin
Ich habe eine Frage zur Kalenderwoche.
Mein Gedanke dahinter ist, ich habe das akt. Datum, lass mir die KW dafür anzeigen über die Funktion Kalenderwoche. Nun möchte ich aber, dass mir die Kalenderwoche angezeigt wird, + 6 woche. D.h. ich gebe 1.12.2014 ein = kw 45
+ 6 = Kw 51
Spinne ich das Rad jetzt weiter mit : 25.12.2014 = KW 52 + 6 Wochen = KW 58, wie bekomme ich das nun hin, dass mir die KW 5 zum Beispiel angezeigt wird, die Kw, des neuen Jahres !?
Vielen Dank für Ihre Hilfe
Hallo Maik,
der Trick dabei ist, die 6 Wochen nicht erst hinterher zu addieren, sondern bereits innerhalb der Funktion. Hier muss man die 6 Wochen allerdings in Tage umrechnen, also 42. Angenommen, das aktuelle Datum steht in Zelle A1, dann würde die Funktion lauten:
=KALENDERWOCHE(A1+42)
Grüße,
Martin
🙂 Man sieht den Wald vor…. ich hatte mir jetzt schon nen 3 Zeiler gebastelt mit wenn dann funktion und < als… 🙂
Vielen lieben Dank !!
Hallo Martin,
ich habe mir in Excel eine Urlaubs-Karte erstellt.
Leider funktioniert es mit der Berechnung der rein benötigten Urlaubs-/Gleittage nicht so wie ich s mir gedacht habe.
was soll ich noch ändern:
Beispiel:
Ich will meinen Urlaub vom 23.12.2015 – 08.01.2016
Nun ist es in meiner Arbeit so,
dass ich für den 24.12. und 31.12. keinen Urlaubs- oder Gleittag nehmen muss.
1.) Wie kann ich Excel sagen, dass es die Tage (24.12./31.12.) nicht mitzählen soll?
Zur Berechnung der reinen Arbeitstage / Gleittage habe ich die Formel „NETTOARBEITSTAGE“ verwendet .
– wwerden aber dann trotzdem mitgezählt.
2.) habe mir zwar unter meine Urlaubs-Tabelle
eine zweispaltige Liste mit Feiertagen (links „Datum“ / rechts „Name des Feiertages“ erstellt), kann ich die in die Urlaubsliste so integrieren, dass Excel die Feiertage erkennt und automatisch rausrechnet?
2.) Wie kann ich variable Feiertage in Excel nicht mitberechnen lassen bzw. auch feste Feiertage nicht mitberechnen lassen, sofern sie auf ein WE fallen?
Vielen Dank
Schönes Wochenende
Andreas
Hallo Andreas,
Die Funktion NETTOARBEITSTAGE zählt grundsätzlich alle Tage außer Samstag und Sonntag, und generell kennt sich auch keine Feiertage, wenn man diese nicht explizit angibt.
zu 1)
Das heißt in Deinem Beispiel also, dass Du den 24.12 und 31.12. also wie Feiertage behandelst (Du brauchst ja dafür keinen Urlaub zu nehmen) und musst sie daher in der Funktion als Feiertag angeben.
zu 2)
Die kannst die Liste mit den Feiertagen einfach in der Funktion angeben. Angenommen, dein Startdatum steht in Zelle A1, dein Enddatum in Zelle A2 und die Liste mit den Feiertagen in A3:A10, dann lautet die Formel:
=NETTOARBEITSTAGE(A1;A2;A3:A10)
zu 3)
Wenn die variablen Feiertage auf ein Wochenende fallen, werden sie von NETTOARBEITSTAGE automatisch ignoriert, da diese Funktion Wochenenden grundsätzlich ausklammert.
Schöne Grüße,
Martin
Hallo!
Ich habe eine Datenbank mit Tonaufnamen in der ist auch das Aufnahmedatum dabei.
Bei einigen fehlt aber das genaue Datum. Das sieht dann so aus in der Datumsspalte:
2010.12.25
2009
2006.05
Wie kann ich so ein Kuddel Muddel ordentlich und brauchbar anzeigen lassen, so dass ich es später auch in Access verwenden kann?
Hallo Prema,
die Frage ist ja, was für ein Datum z.B. bei „2009“ eingetragen werden soll. Der 01.01.2009? Und bei „2006.05“ der 01.05.20015?
Dann ginge das über eine WENN-Abfrage. Angenommen, das Datum steht in A1, dann könnte die Formel in B1 lauten:
=WENN(LÄNGE(A1)=7;A1&“.01″;WENN(LÄNGE(A1)=4;A1&“.01.01″;A1))
Grüße,
Martin
Hallo Martin,
ich suche eine Lösung für die Berechnung des jeweils 4. Sonntags im Monat, dabei bin ich Ihre interessante Seite gefunden.
Wenn Sie eine Lösung für mich hätten wäre toll.
Viele Grüße
Richard
Hallo Richard,
unter der Annahme, dass in Zelle A1 der 01.01. des jeweiligen Monats steht (also z.B. 01.01.2016), liefert diese Formel den 4. Sonntag im jeweiligen Monat:
=A1+21+7-WOCHENTAG(A1;11)
Grüße,
Martin
Hallo Martin,
herzlichen Dank für Deine schnelle Hilfe!
Grüße
Richard
Hallo Martin,
eine Frage zur Berechnung der Nettoarbeitstage. Ich habe ein Anfangs- und ein Enddatum. Es kommt vor, dass eines dieser Daten auf einem Samstag oder Sonntag liegt. Ich möchte aber, dass, liegt Anfangs- oder Enddatum auf einem Wochenendtag, der kommende Montag als Anfangs- bzw. Enddatum zur Berechnung der Nettoarbeitstage gesetzt wird. Habe es mit einer Verknüpfung zu der Funktion Wochentag versucht (=wenn(Wochentag () > 5; 1, Wochentag()). Komme damit aber nicht wirklich weiter. Kennst Du eine Lösung für diese Fragestellung?
Vielen Dank im Voraus
Susi
Hallo Susi,
Dein Ansatz ist schon richtig. Unter der Annahme, dass das Anfangsdatum in A2 und das Enddatum in B2 steht, kannst Du das neue Anfangs- und Enddatum wie folgt berechnen:
=WENN(WOCHENTAG(A2;11)<6;A2;WENN(WOCHENTAG(A2;11)=6;A2+2;A2+1))
=WENN(WOCHENTAG(B2;11)<6;B2;WENN(WOCHENTAG(B2;11)=6;B2+2;B2+1))
Und darauf kannst Du dann die Nettoarbeitstage ermitteln.
Grüße,
Martin
Hallo Martin,
prima! Das funktioniert.
Vielen Dank!
Hallo Martin,
ich verzweifle an einer Datumsfunktion.
Folgendes möchte ich erreichen:
– Wenn ein Beginndatum auf den 01. eines Monates fällt, dann soll er einfach 3 Jahre hinzuaddieren für das Ablaufdatum.
(Das habe ich hinbekommen)
– Fällt das Datum auf den 2. bis 31. eines Monates, soll der Ablauf auf den nächsten ersten + 3 Jahre aufgerundet werden.
bsp.: Beginn 15.02.2016 Ablauf 01.03.2019
Kannst du mir helfen?
Hallo Daniel,
versuch’s mal hiermit:
=WENN(TAG(A1)=1;DATUM(JAHR(A1)+3;MONAT(A1);TAG(A1));DATUM(JAHR(A5)+3;MONAT(A5)+1;1))
Annahme ist, dass das Beginndatum in Zelle A1 steht.
Grüße,
Martin
Perfekt!
Vielen lieben Dank
Hallo Martin,
sehr umfangreicher Beitrag, herzlichen Dank dafür.
Ich habe folgende Fragestellung und komme nicht ganz weiter.
In einem Projektplan habe ich sowohl einen Anfangs, wie auch einen Endtermin in 2 verschiedenen Zellen (ganzes Datum, sprich, 01.03.2016) definiert. In einer anderen Zelle habe ich einen Status definiert, mit einer bedingten Formatierung (Bsp. steht für die Aufgabe in der Spalte „Status“ „In Bearbeitung“, so wird „In Bearbeitung“ gelb eingefärbt.
Nun habe in der Tabelle auch die Kalenderwochen angelegt, für jede KW eine Spalte mit der entsprechenden Nummer.
Mein Ziel: Erkenne das Enddatum und den Status und Färbe in der entsprechenden KW Woche in der entsprechenden Farbe die Zelle ein.
Beispiel:
C3=15.03.2016
E3= „In Bearbeitung“ = Gelbe Schrift
Ziel: L3 gelb einfärben (da in L3 die entsprechende KW für den 15.03.2016 wäre)
Für mich würde es auch reichen, wenn er nur das Enddatum ermittelt und die Zelle irgendwie einfärbt, falls das andere nicht gehen sollte.
Kannst Du da evtl. weiter helfen?
Viele Grüße,
Julia
Hallo Julia,
Annahme:
In Zeile 1 stehen ab Spalte L die Kalenderwochen (als Zahl): L1 = 11, M1 = 12, N1 = 13 usw.
Dann den Bereich L3:N3 markieren (bzw. alle zusätzlichen Spalten und Zeilen, die bei Dir zum Einsatz kommen) und folgende Formatierungsregel eingeben:
=UND(KALENDERWOCHE($C3;21)=L$1;$E3=“In Bearbeitung“)
Und dann eben die gewünschte gelbe Formatierung festlegen.
Wichtig ist nur, dass Du auf die korrekten Bezüge achtest, also die $-Zeichen richtig setzt.
Grüße,
Martin
Hallo Martin,
herzlichen Dank für das Feedback. Zum Testen habe ich das Szenario mal in einer neuen Tabelle nachgebaut und bekomme beim Einfügen der Formatierungsregel folgende Meldung: Verweisoperatoren (wie etwa Vereinigungen, Schnittmengen und Bereiche) oder Arraykonstanten dürfen in dem Kriterium Bedingte Formatierung nicht verwendet werden.
Mein Vorgehen: Habe die entsprechenden Zellen markiert, bin auf „Neue Regel“ gegangen, dort auf „Formel zur Ermittlung der zu formatierten Zellen verwenden) und habe in dem Wertefeld dann die Formel eingetragen.
Wo ist mein Fehler?
Vielen Dank und Gruß,
Julia
Hallo Julia,
ich vermute mal, Du hast die Formel von mir direkt in das Eingabefeld kopiert. Da wurden möglicherweise die Anführungszeichen im dem Teil „In Bearbeitung“ falsch übernommen. Lösche die mal raus und setze sie von Hand wieder ein.
Grüße,
Martin
Hallo Martin,
jap, das war das Problem. Die sind so klein, das hab ich gar nicht gesehen 🙂
Danke für die Hilfe,
viele Grüße,
Julia
Hallo Martin,
Du hast mir schon mal sehr erfolgreich bei meiner bedingten Formatierung geholfen. Gerade bin ich bei einem weiteren Problem mit meinem Kalender.
Die Formel für die Einfärbung der Zelle:
=UND((KALENDERWOCHE($J10;21)=R$8;$N10=“in Bearbeitung“) -> er färbt die Zelle entsprechend der Wörter ein, die in der Zelle stehen und das in der entsprechenden KW, quasi deine Formel angepasst auf meine Tabelle.
Das gleiche habe ich jetzt noch mal gemacht, für das Enddatum, sprich er färbt z.B. KW 26 und KW 28 ein. Nun möchte ich, dass er auch die Zellen dazwischen einfärbt, wie bei einem Gantt Diagramm.
Ich habe es schon hinbekommen, allerdings wenn nur mit einer Farbe. Mein Gedanke war, die Zellen vergleichen zu lassen per R$8>=$H10;R$8 das funktioniert an sich (glaube ich), aber eben nicht verknüpft mit der Formel oben.
Die KW des Kalenders stehen in Zeile 8, beginnend bei Spalte R (bis BB).
Die KW des Startdatums stehen in Spalte H, beginnend in Zeile 10.
Die KW des Enddatums stehen in Spalte K, beginnend in Zeile 10.
Der Bereich für den die Formel gelten soll: =$R$10:$BB$290
Ich habe das dumpfe Gefühl, dass es zwar in der Idee richtig ist, aber die bedingte Formatierung da nicht ganz geht. Vermutlich würde es auch über VBA gehen, aber das wäre eher die letzte Option, da ich mich da mehr einarbeiten müsste 🙂
Vielen Dank und Gruß,
Julia
Hallo Julia,
das geht auf jeden Fall ohne VBA. Wenn ich Deine Tabelle bei mir richtig umgesetzt habe, kannst Du mit folgender Formatierungsregel die Einfärbung hinbekommen:
=UND(R$8>=$H10;R$8<=$K10) Vorher natürlich den Bereich markieren, für den dir Regel gelten soll (R10:BB290). Und wichtig sind die Dollarzeichen an der richtigen Stelle. Schöne Grüße, Martin
Hallo Martin,
aus irgendeinem Grund fehlt mir bei Deiner Antwort der „Antwort“ Button, daher muss ich hierrüber antworten 🙂
Ja, das geht, soweit war ich auch schon. Problem ist ja die Verknüpfung zu der bestehenden Formel der Einfärbung, je nachdem welcher Text drin steht. Sprich die verbundenen Zellen im Kalender können ja unterschiedliche Farben haben. Formel: =UND((KALENDERWOCHE($J10;21)=R$8;$N10=”in Bearbeitung”) -> er färbt sie Gelb. Für einen anderen Status färbt er sie z.B. blau. Aber die Verknüpfung der beiden funktioniert nicht.
=UND((KALENDERWOCHE($J10;21)=R$8;$N10=”in Bearbeitung”;R$8>=$H10;R$8<=$K10). Vielleicht habe ich da die Verknüpfung falsch? Oder geht das einfach nicht?
Vielen Dank und Gruß,
Julia
Hallo Julia,
da die erste Bedingung Deiner UND-Verknüpfung genau eine bestimmte Kalenderwoche abfragt, funktioniert das Einfärben für einen längeren Zeitraum nicht. Versuche es mal hiermit (jeweils eine eigene Regel):
=UND(R$8>=$H10;R$8<=$K10;$N10="in Bearbeitung") --> Gelb einfärben
=UND(R$8>=$H10;R$8<=$K10;$N10="erledigt") --> Blau einfärben
usw.
Schöne Grüße,
Martin
Hallo Martin,
hat geklappt, herzlichen Dank!
Viele Grüße,
Julia
Hallo,
vielleicht kann mir hier ja jemand helfen. Mit der Arbeitstaggefunktion klapptdas nicht, da ich auch am Wochenende arbeite. Ich habe einen turnus von 4 Tage Arbeit 2 Tage frei 4 Tage arbeit 2 Tage frei 5 Tage arbeit 1Tag frei das möchte ich farblich makieren und wenn möglich auch alle Feiertage Rot hinterlegen. Den alteen Turnus hatte ichmit der Restformel bearbeiten können der war abrauch nur 6/2 5/3 das war einfach nnach mehrstündigem Kopfzerbrechen, aber hier das muß irgendwie verschachtelt werden bin schon seit wochen immer wieder am probieren komm aber nicht vom fleck. Kann mir jemand helfen???
Hallo Astrid,
das ist eine etwas kniffelige Angelegenheit 🙂
Ein mögliche Lösung könnte so aussehen: Angenommen, die Kalendertage sind im Bereich ab A2 eingetragen. Und weiterhin angenommen, in Zelle C1 steht das Datum, an dem der erste Arbeitstag eines 18-Tage-Zyklus beginnt (AAAA-FF-AAAA-FF-AAAAA F):
Dann markiere die Datumseinträge ab Zelle A2 und lege in der bedingten Formatierung eine neue Regel mit folgender Formel an:
=WAHL(WENN(A2-$C$1<18;A2-$C$1;WENN(REST(A2-$C$1;18)>0;REST(A2-$C$1;18);REST(A2-$C$1;18)+1))+1;“A“;“A“;“A“;“A“;“F“;“F“;“A“;“A“;“A“;“A“;“F“;“F“;“A“;“A“;“A“;“A“;“A“;“F“)=“F“
Und vergib das gewünschte Format, z.B. grüne Füllung.
Für die Sonntage brauchst Du dann noch eine zweite Formatierungsregel:
=WOCHENTAG(A2;11)=7
Und vergibst hier z.B. eine rote Schrift.
Dann sieht es so aus:
Hilft das?
Schöne Grüße,
Martin
Hallo Martin
Kennst Du eine Formel die die gleiche Funktion oder änlich hat wie diese?
=IF(WEEKDAY(U$4;2)=6;U22;““)
Gruß Robert
Hallo Robert,
die Formel prüft ja, ob das Datum in U4 auf einen Samstag fällt und gibt für diesen Fall den in U22 stehenden Wert zurück. In deutsch:
=WENN(WOCHENTAG(U$4;2)=6;U22;““)
Aber da ich nicht weiß, wie Deine Tabelle sonst noch aufgebaut ist, was in U22 steht und was Du genau bezwecken möchtest, kann ich dir hier leider keine Alternative anbieten.
Grüße,
Martin
Hallo Martin,
Erstmal Vielen Dank für die schnelle Reaktion 🙂
Das ist richtig ich prüfe hiermit das datum ob es ein samstag oder sonntag ist und gebe dann den wert vom Sa od. So zurück und genau das möchte ich anhand vom Feiertag erreichen.
ich Arbeite für eine Firma mit Excel seit einer langer zeit und führe ein sogenanntes Leistungserfassung der Firma.
Um zellen Manuell auszufüllen an Samstage und Sonntage nutze ich diese Formel Nur Feiertage habe ich noch keine
funktion hinbekommen.
Also sagen wir mal am 1.06.16 ist Mittwoch und ein Feiertag und wir haben in der Spalte U22 eine Zahl die ich nochmals in die Zelle
U32 schreiben will bisher mache ich das Manuell…
Datum: 01.06
Mo – Fr: Mittwoch -> 200000 zahl das wäre die Grund summe plus
Mittwoch -> Feiertag der Zuschlag und dazu muss hier die zahl 20000 nochmals geschrieben werden. ( bisher doppelt von hand geschrieben)
Ich hoffe Du verstehst nun was ich meine 🙂 sonst gebe ich dir ein beispiel.
“ Die Formel prüft ja, ob das Datum in U4 auf einen Samstag fällt und gibt für diesen Fall den in U22 stehenden Wert zurück. In deutsch:
=WENN(WOCHENTAG(U$4;2)=6;U22;””) “
Grüße Robert
Hallo Robert,
Excel kennt grundsätzlich keine Feiertage, Du musst also selbst eine Liste mit dem Datum aller relevanten Feiertage erstellen. Daneben schreibst Du die jeweiligen Zuschläge und dann kannst Du auf diese Liste zum Beispiel über eine SVERWEIS-Funktion zugreifen.
Schöne Grüße,
Martin
Hallo Martin,
Sorry das ich jetzt nochmal nerven muss. Aber die Idee von dir bekomme ich so nicht umgesetzt. Irgend was mach ich Falsch glaub ich.
Hast Du die Mail von mir bekommen unter der Adresse von deinem Impressum? Da hab ich ein Bespiel dir als Anhang zukommen lassen. Ich brauche deine Hilfe wenn es möglich wäre.
Vielen Dank Schon mal!
Schöne Grüße Robert
Hallo Martin,
Ich habe ein Beispiel zusammen gebaut damit Du sehen kannst was genau ich meine. Wie soll ich die dir zukommen lassen?
Gruß Robert
Hallo Martin,
ich glaube ich weiß nun was ich tun muss.. Vielen Dank! Probiere das nun aus! Du bist klasse Danke!
Gruß
Hallo Martin,
einfach genial was man bei Dir für Kniffe mit Excel findet !!
Bei einem Problem komme ich allerdings absolut nicht weiter. Ich habe nach Makros gesucht etc …..
Ich möchte eine Tabelle erstallen, in der in einem Referenzfeld der Monat und das Jahr eingetragen wird.
Nun soll im Feld „A5“ der zu „B5“ gehörende Wochentag und in „B5“ das Datum beginnend mit dem 1. des Monats eingetragen werden.
Wenn ich also im Referenzfeld den Monat / Jahr änder soll sich diese Tabelle NEU füllen ……
Hallo Lutz,
freut mich, wenn die Artikel weiterhelfen. Für das, was Du suchst, sind keine Makros notwendig (außer ich verstehe Dich völlig falsch).
Nehmen wir an, Du gibst im Referenzfeld A1 „Juli 2016“ ein. Excel wird diesen Wert automatisch als Datum interpretieren, und zwar als den Monatsersten (also den 01.07.2016). Somit brauchst Du in den Zellen A5 und B5 nur einen Verweis auf A1 setzen und die Zelle A5 über ein benutzerdefiniertes Zahlenformat als Wochentag formatieren (Formatcode = TTT)
Oder habe ich Dich doch falsch verstanden?
Schöne Grüße,
Martin
Hallo Martin
Ich versuche, einen Jahreskalender zu erstellen, bei dem die Wochentage pro Zeile und die Monate pro Spalte definiert sind. Die ersten 7 Zeilen enthalten deshalb die Funktion =WENN(ISTZAHL(B3);B3+1;WENN(WOCHENTAG(B$1;2)=1;B$1;““))
->für die Montagszeile, „=2″ für die Dienstagszeile usw.
(so erscheint an den ersten 7 Wochentagen das korrekte Datum und kann nach rechts in die Folgespalten/-monate übertragen werden)
Nach der ersten Woche ist es eh klar, dass bereits ein Datum über der Funktion steht. Deshalb habe ich es so angepasst:
=WENN(MONAT(B10)=MONAT(B10+1);B10+1;““)
(so vermeide ich, dass der Folgemonat in dieser Spalte aufgeführt wird)
Das einzige was mich jetzt noch ärgert: Nach dem letzten des Monats (der ja je nach Monat auf verschiedenen Zeilen liegen kann), erscheint eine leere Zelle und danach #Wert. Ich hatte auch schon Varianten, in denen statt #Wert FALSCH erscheint. Ich möchte aber, dass die Zellen nach Monatsende leer bleiben.
Hallo Sandra,
ich habe versucht, aus den Formeln schlau zu werden, aber ich kann mir momentan beim besten Willen nicht vorstellen, wie Dein Kalender tatsächlich aussieht. Tut mir leid, wenn ich ein wenig auf dem Schlauch stehe. Wenn Du willst, dann schicke mir die Datei per E-Mail (Adresse steht im Impressum), dann werfe ich gerne mal einen kurzen Blick darauf.
Schöne Grüße,
Martin
Geschickt – gelöst – in doppeltem Sinne. Perfekt! Vielen herzlichen Dank.
Gern geschehen 🙂
Hallo Martin
ich habe ein Tabelle in der in spalt A das Datum mit=WENN(WOCHENTAG(A2800)=6;A2800+3;A2800+1) kann ich in der selben Spalte auch die Kalender Woche mit ausgeben?
Hallo Benjamin,
ja, das geht. Wenn es wirklich in die gleiche Spalte mit dem Datum soll, muss die Formel allerdings ein wenig aufgebohrt werden. Zum Beispiel so:
=WENN(WOCHENTAG(A2800)=6;“KW „&KALENDERWOCHE(A2800+3;21)&“,“&TEXT(A2800+3;“TT.MM.JJJJ“);“KW „&KALENDERWOCHE(A2800+3;21)&“,“&TEXT(A2800+1;“TT.MM.JJJJ“))
Schöne Grüße,
Martin
hallo,
können Sie mir bitte helfen. Ich brauche eine Formel nur für Wochenende
02.07. – 03.07.16
09.07. – 10.07.16
16.07. – 17.07.16
u.s.w. bis ende des Jahres.
Danke schön.
Stefan
Hallo Stefan,
eine Formel habe ich jetzt spontan nicht. Ich würde einfach jeweils die ersten beiden Samstage und Sonntage in eine eigene Zelle schreiben und dann an der kleinen schwarzen Ecke rechts unten so weit wie benötigt nach unten ziehen. Excel erweitert dann die Serie automatisch. Etwa so:
Schöne Grüße,
Martin
hallo,
danke schön.
Stefan
Hallo Martin,
ich habe folgendes Problem: Ich will ein Anfangsdatum bis zu einem Enddatum in Tage umrechnen. Ich habe dazu folgende Formel gefunden:
=DATEDIF(„Anfangsdatum“;“Enddatum“;“T“)
=DATEDIF(Anfangsdatum;Enddatum;“T“)
Leider funktioniert bei mir die Eingabe der Anführungsstriche nicht. Sobald ich diese einfügen will, bekomme ich einen Fehler angezeigt. Excel schlägt mir dann vor, ich soll unter Funktionen/Formeln gehen oder nur den Gedankenstrich ‚ einsetzen. Hab alles probiert, nichts funktioniert. Wenn ich auf Enter drücke steht #ZAHL!
Was mache ich falsch?
Hallo EKr,
nimm beim letzten Parameter die englischen Abkürzungen, also z.B. „D“ statt „T“ für Tage. Dann sollte es klappen.
Schöne Grüße,
Martin
Hallo Martin,
Danke, jetzt geht die Formel. Aber als Ergebnis steht Null.
Ich habe folgendes eingegeben:
=DATEDIF(02.10.2006;31.07.2009;”D”)
Warum kommt dann 0 raus?
LG EKr
… ach ja, und das mit den Anführungsstrichen in der Formel funktioniert auch nicht. Die lässt Excel mich nicht eingeben. Ich hab also folgendes eingegeben:
=DATEDIF(02.10.2006;31.07.2009;”T”)
Und dann kommt als Ergebnis 0 heraus.
Warum?
LG EKr
Hallo EKr,
wenn Du die Datumsangaben direkt in die Funktion einträgst, musst Du diese ebenfalls in Anführungszeichen setzen. Für Dein Beispiel also
=DATEDIF(„02.10.2006″;“31.07.2009″;“D“)
Alternativ kannst Du natürlich Zellbezüge verwenden, in die Du dann die beiden Datumsangaben schreibst. Beispiel: Anfangsdatum steht in Zelle A1, Enddatum in B1. Dann lautet die Formel:
=DATEDIF(A1;B1;“D“)
Noch ein Tipp: Wenn Du das Beispiel hier aus dem Kommentar kopierst, stimmen die Anführungszeichen nicht (die werden hier auf dem Blog leider falsch dargestellt). Gib stattdessen die Anführungszeichen von Hand ein (also die, die auf der Tastatur über der Ziffer 2 stehen)
Schöne Grüße,
Martin
Hallo Martin
Deinen Tipp mit den Arbeitstagen und freie Tage konnte ich bereits gut umsetzen. Leider konnte ich bis anhin nicht herausfinden, wie ich einen bestimmten Wochentag errechnen kann. Beispiel: Ausgangsdatum (Verarbeitung) ist der 03.08.2016, die Produktion findet dann jeweils am darauffolgenden DI (09.08.) oder DO (04.08.) statt. Nach dem Produktionsdatum muss ich das Lieferdatum errechnen, was ich mit plus 6 Arbeitstage eingebe.
Der Produktionstag, welcher immer an einem DI oder DO stattfindet, bereitet mir Schwierigkeiten. Gibt es eine Möglichkeit, die Formel so einzugeben, dass er nur Dienstage oder Donnerstage berücksichtigt?
Viele Dank für deinen Bescheid.
Grüsse, Salvatore
Habe in der Zwischenzeit folgende Formel eingegeben, wenn der Produktionstag immer am Donnerstag erfolgen soll: =ARBEITSTAG.INTL(B13;3;1110111;$B$4:$B$9). Jetzt steht der Fehler „ZAHL!“. Diese Formel funktioniert nur mit der 0 bei der Position „Tage“. Wo mache ich hier den Fehler?
Gruss, Salvatore
Hallo Salvatore,
in Deiner Formel steckt ein kleiner Fehler: Du musst die Wochenendzeichenfolge in Anführungszeichen setzen:
=ARBEITSTAG.INTL(B13;3;“1110111″;$B$4:$B$9)
Dann sollte es klappen.
Schöne Grüße,
Martin
Vielen Dank Martin
ich bin immernoch an der selben Formel hängengeblieben, die so wie ich vorgängig beschrieben habe, nicht ganz korrekt ist. Jetzt kommt ein anderer Faktor hinzu, den ich vorher nicht kannte: Wenn die Verarbeitung zwischen MO-MI stattfindet, ist der Produktionstag der Donnerstag der darauf folgenden Woche. Wenn die Verarbeitung am DO+FR stattfindet, ist der Produktionstag auch der Donnerstag der folgenden Woche.
Folgende Formel habe ich nun versucht, die sich leider als Falsch herausstellt:
=WENN(WOCHENTAG(1-3;2);ARBEITSTAG.INTL(B13;2;“1110111″;$B$4:$B$9);ARBEITSTAG.INTL(B13;1;“1110111″;$B$4:$B$9))
Ich weiss leider nicht, wie ich die Formel für Wochentag (MO-MI) eingeben soll…
Weisst du hier einen Rat?
Grüsse, Salvatore
Hallo Salvatore,
um mehrere Wochentage abzufragen, kannst Du die Funktion in eine UND-Bedingung packen, z.B. so:
=WENN(UND(WOCHENTAG(B14;2)>=1;WOCHENTAG(B14;2)<=3);..dann..;..ansonsten...) Aber das alleine wird Dich wahrscheinlich noch nicht weiterbringen. Wenn ich Dich richtig verstanden habe, soll bei Wochentagen von Montag bis Mittwoch der Donnerstag (der gleichen Woche) ausgegeben werden und bei Wochentagen ab dem Donnerstag soll der Dienstag (der folgenden Woche) ausgegeben werden. Richtig? Dann wäre folgende Formel eine mögliche Lösung: =WENN(UND(WOCHENTAG(B13;2)>=1;WOCHENTAG(B13;2)<=3);B13+4-WOCHENTAG(B13;2);B13+9-WOCHENTAG(B13;2)) Schöne Grüße, Martin Grüße, Martin
Hallo Martin
Die Formel passt! Hab vielen Dank Martin.
Habe die Formel nur kurz so angepasst, dass folgende Ausgabe berechnet wird:
bei Wochentagen von Montag bis Mittwoch = der Donnerstag (der folgenden Woche)
bei Wochentagen ab dem Donnerstag = „derselbe“ Donnerstag (der folgenden Woche)
Viele Grüsse
Salvatore
Hallo Martin,
wirklich interessante Formeln die hier vorgestellt werden. Ich habe gesehen, dass sie auch bei Problemen weiterhelfen.
Vielleich können Sie mir auch bei einem Excel Problem weiterhelfen. Bisher habe ich das immer manuell bearbeitet,
da ich keine Formel dafür gefunden habe.
Folgender Fall:
Ich habe verschiedene Fälligkeitsdaten und möchte immer den betreffenden Monat dabei stehen haben.
Die Besonderheit ist, dass alle Datums bis zum 3. Arbeitstag des Folgemonats noch den Vormonat enthalten sollen.
Zum Beispiel:
05.07 – 03.08.2016 oder 04.08. – 05.09.2016
04.08.2016 August
03.08.2016 Juli –> die Besonderheit
20.07.2016 Juli
Ich hoffe sehr das Sie mir hier weiterhelfen können.
Vielen Dank für Ihre Mühen im Voraus.
Mit freundlichen Grüßen,
Stefanie Metzger
Hallo Stefanie,
angenommen, das Datum steht in Spalte A, dann liefert folgende Formel in Spalte B den gewünschten Monat (als Zahl):
=MONAT(ARBEITSTAG(A1;-3))
Und wenn der Monat als Wort benötigt wird, hilft
=DATUM(2016;MONAT(ARBEITSTAG(A1;-3));1)
Die Formatierung muss hier auf „MMMM“ eingestellt werden.
Schöne Grüße,
Martin
Hallo Martin,
du hast bereits so viele Lösungen angegeben, aber leider konnte ich keine für mein Problem finden. Es wäre toll wenn du mir hilfst.
Ich möchte ein Zahldatum für Rechnungen errechnen, bisher habe ich die Arbeitstagformel verwendet. Gegeben ist bspw. 14 Tage Zahlungsziel und Eingangsdatum. Ich benötige jetzt eine Formel die ein Zahldatum innerhalb des Zahlungsziels (z.B. 14-Tage) auf einen Montag oder Donnerstag errechnet.
Bin leider auf keine Lösung gekommen.
Viele Grüße
Anna
Hallo Anna,
wenn ich Dich richtig verstanden habe, sollen auf das Eingangsdatum 14 Tage addiert werden. Wenn das Zahlungsdatum auf einen Freitag, Samstag oder Sonntag fallen würde, dann soll stattdessen der darauffolgende Montag genommen werden.
Eine mögliche Lösung wäre die folgende (Annahme: Eingangsdatum steht in Zelle A1):
=A1+14+WENN(WOCHENTAG(A1+14;11)>4;8-WOCHENTAG(A1+14;11);0)
Schöne Grüße,
Martin
Hallo Martin,
danke für deine Antwort. Das ist es leider nicht ganz. Habe noch etwas mit deiner Formel rumprobiert, aber hat nicht geklappt.
Ich habe es auch nicht wirklich gut erklärt. Sorry. Also ich meinte wenn die Rechnung eingeht dann sollen 14 Tage addiert werden. Ist der Tag Datum (Nach der Addition) dann ein Montag (01.08.16), Dienstag (02.08.16), Mittwoch (03.08.16), dann soll das Zahldatum der Montag der 01.08.2016 sein. Wenn das Datum auf den Donnerstag (04.08.16), Freitag (05.08.16), Samstag (06.08.16), Sonntag (07.08.2016) fällt, dann soll das Zahldatum der Donnertag 04.08.16 sein.
Ich würde mich freuen wenn du mir nochmal hilfst.
Viele Grüße
Anna
Hallo Martin
Ich suche nach einer Formel mit der ich eine Frist berechnen kann.
Ausgehend von einem Eingangsdatum soll eine Frist berechnet werden. Die Dauer ist immer 30 Tage (Wochentage) ab Eingangsdatum. Diese Formel wäre grundsätzlich keine Hexerei, nun habe ich aber noch zwei weitere Vorgaben, für die ich noch keine passende Formel gefunden habe.
1. Fällt das Fristdatum auf einen Feiertag oder einen speziell definierten Tag (gemäss separater Spalte), soll die Frist automatisch auf den nächst möglichen Wochentag verlängert werden.
2. Fällt das Fristdatum auf einen Samstag oder Sonntag, soll die Frist automatisch auf den darauffolgenden Montag fallen
Hast du dafür eine Lösung?
Mfg Philip
Hallo Philip,
genau das macht die oben im Artikel beschrieben Funktion ARBEITSTAG (bzw. die bessere Funktion ARBEITSTAG.INTL)
Die addiert zu einem Ausgangsdatum eine bestimmte Anzahl an Arbeitstagen und liefert dann das Zieldatum. Wochenenden werden automatisch ausgeklammert und man kann zusätzlich noch eine Liste mit weiteren freien Tagen definieren.
Schöne Grüße,
Martin
Lieber Martin
Damit konnte ich das Problem tatsächlich lösen!
Vielen herzlichen Dank und freundliche Grüsse
Philip
Hi,
ich versuche aus einem Zeitraum 01.02.2012 Start – 05.03.2015 Ende mir ausgeben zu lassen, in welchem Jahr, wieviel arbeitstage (ohne wochenende) waren. Der output sollte also z.B,. so sein 2012 300 Tage ; 2013 365 Tage ….
Ich scheitere aber an der Auswertung der einzeljahre.
Kannst du vielleicht helfen?
Gruß
T Kasi
Hallo Thorsten,
ich fürchte, mit einer einzigen Formel wird das nicht möglich sein, zumindest fällt mir keine passende Lösung dazu ein. Aber vielleicht hat ja noch ein anderer Leser eine gute Idee.
Schöne Grüße,
Martin
Hallo Martin,
ich habe dein Forum durchstöbert auf der Suche nach einen Erg. für meine Anforderung mit welcher ich nicht weiter komme.
Ich habe folgenden Sachverhalt:
Es gibt ein Startdatum, Enddatum und Planbeginn für eine Aufgabe. Jede Aufgabe hat eine Dauer hinterlegt und pro Tag kann 7 Stunden an der Aufgabe gearbeitet werden.
Nun möchte ich die Dauer auf die Arbeitstage aufschlüsseln. Sieben Stunden pro Tag. Wenn kein Planbeginn eingetragen ist, soll vom Endtermin rückwärts eingetragen werden. Wenn dieser gesetzt ist (Planbeginn) soll jedoch von diesem vorwärts gerechnet werden. Dabei müssen Wochenenden bzw. Feiertage mit berücksichtigt werden.
Ich komme nur leider mit meinem Ansatz nicht weiter, vielleicht kann ich dich kurz um Hilfe bitten?
Die Datei sende ich Dir mal per Mail. Vielen lieben Dank für die Unterstützung
Hallo Andre,
das scheint mir eine relativ komplexe Angelegenheit zu sein. Vielleicht hat ja einer der Leser hier eine Idee für eine Lösung. Ich habe die Datei mit Andre’s Einverständnis hier zum Download bereitgestellt:
Visualisierung_Verteilung_Arbeittage_Zeiten.xlsx
Schöne Grüße,
Martin
vielen lieben Dank Martin
hallo Martin,
feststehendes Datum ist der 01.11.2023,
jetzt möchte ich einmal immer ab heute bis zu diesem Tag die Anzahl der Kalendertage ermitteln
und zum anderen die Anzahl der Arbeitstage.
bin leider nicht der große Formelfreak, meine bisherigen Versuche sind leider gescheitert 🙁
Grüße
Wolle
Hallo Wolle,
angenommen, das feststehende Datum befindet sich in Zelle A1.
Formel für die Kalendertage: =A1-HEUTE()
Formel für die Arbeitstage: =NETTOARBEITSTAGE(HEUTE();A1)
Beides natürlich als Zahl formatieren.
Schöne Grüße,
Martin