Datums- und Zeitfunktionen - WordPress.com · 2019-03-01 · Excel – Datums- und Zeitfunktionen...

Datums- und Zeitfunktionen

Andreas Thehos at IT-Training & Beratung, 2017-2019

Stand: 28. Februar 2019

Excel – Datums- und Zeitfunktionen

Andreas Thehos, 2017-2019 1 / 26

Inhaltsverzeichnis

Es war einmal… ........................................................................................................................... 2

1 Einleitung ............................................................................................................................ 4

2 Datumsberechnungen ........................................................................................................ 5

2.1 Grundlagen ................................................................................................................ 5

2.2 Eingabe von Datumswerten ...................................................................................... 8

2.3 Formatierung von Datumswerten ............................................................................. 9

2.4 Rechnen mit Datumswerten .................................................................................... 10

2.5 Funktion TAGE für Datumsdifferenz in ganzen Tagen ............................................. 11

2.6 Automatisch den aktuellen Zeitpunkt ermitteln ..................................................... 12

2.7 Funktionen JAHR, MONAT und TAG ........................................................................ 13

2.8 Funktion DATUM – Datumswerte „zusammensetzen“ ........................................... 13

2.9 Funktion DATEDIF – Datumsdifferenzen in Jahren, Monaten und Tagen ............... 16

2.10 Unterschiede zwischen TAGE, einfacher Formel und DATEDIF ............................... 17

2.11 Kalenderwoche ermitteln – KALENDERWOCHE und ISOKALENDERWOCHE ........... 18

2.12 Texte in Datum konvertieren mit DATWERT ........................................................... 20

2.13 Das Ende eines Monats errechnen – MONATSENDE .............................................. 21

2.14 Arbeitstage ermitteln – NETTOARBEITSTAGE und NETTOARBEITSTAGE.INTL ........ 21

3 In eigener Sache ................................................................................................................ 22

4 Videolinks .......................................................................................................................... 23

5 Abbildungsverzeichnis ...................................................................................................... 26



Es war einmal… Den 15. September 2016 werde ich wohl immer im Hinblick auf Datumsfunktionen im Gedächtnis behalten!

Ich befand mich an diesem Tag in einem Beratungstermin, während auf einmal das Mobiltelefon meines Kunden klingelte… keine Details… aber da wartete ein verzweifelter Kunde am Hafen einer amerikanischen Metropole auf seine dringend benötigte Ware. Vereinbart gewesen sei Kalenderwoche 38. Entspannte Gesichter… unser dreiteiliger Monatsplaner zeigte für diesen Tag Kalenderwoche 37 an, die Waren seien gerade in Hamburg verladen worden.

Jedoch wurde von einer der Parteien bei der Terminabstimmung nicht der kulturelle Unterschied zwischen den USA und Deutschland in der Berechnungsweise der Kalenderwochen bedacht. In den USA war tatsächlich schon Kalenderwoche 38. Bei uns immer noch Kalenderwoche 37.

Inhaltlich wird unsere Kalenderwoche von der ISO 86011 bzw. DIN EN 28601 geregelt. Darin ist geklärt, dass eine Woche montags startet und Kalenderwoche 1 diejenige Woche ist, in die der 4. Januar fällt, die also praktisch mehr Tage des neuen Jahres beinhaltet – bzw. wenn in der Woche auch der Donnerstag im neuen Jahr liegt.

Abb. 1: Kalenderwoche nach DIN EN 28601

In den USA beginnt Kalenderwoche 1 automatisch am 1. Januar – das ist praktisch und leicht zu merken. Der 31. Dezember liegt in Kalenderwoche 52 oder 53. Zudem gibt es zwei Typen von Wochen. Typ 1 zählt eine Woche von Sonntag bis Samstag2, Typ 2 zählt wie bei uns von Montag bis Sonntag.

1 https://de.wikipedia.org/wiki/ISO_8601 2 https://de.wikipedia.org/wiki/Sonntag



Abb. 2: Kalenderwoche US vs. ISO

Eine Übersicht über die üblichen Arbeitstage eines Landes ist auch bei Wikipedia3 zu finden. Dass auch der Begriff Woche4 nicht einheitlich definiert ist, lässt sich dort ebenfalls nachlesen.

Zudem werde der Begriff Kalenderwoche im Amerikanischen eher selten genutzt, eher wird die Woche des entsprechenden montags genannt – „week of september, 12“ anstatt von „workweek 38“ hätte hier wohl Kommunikationsprobleme vermeiden helfen können.

Das Projekt konnte zum Glück auch mit der kleinen Lieferverzögerung weitergeführt werden, hat uns aber etwas ganz deutlich vor Augen geführt:

! Ich darf Excel nicht einfach blind vertrauen und muss meine intuitive Nutzung hinterfragen. Ich muss wissen, mit welchen Grundeinstellungen bzw. Annahmen arbeitet Excel und was bewirken die einzelnen Parameter der Funktionen.

Die Excel-Funktion KALENDERWOCHE nutzt ohne weiteren Parameter das amerikanische Datumssystem 1 mit dem Wochenstart auf dem Sonntag. Parameterwert 2 berechnet die amerikanische Woche ab dem Montag. Nur Parameter 21 ab Excel 2010 bzw. die seit Excel 2013 eingeführte Funktion ISOKALENDERWOCHE nutzen unsere Kalenderwochen-bezeichnung nach Datumssystem 2.

? Woher soll man denn so etwas wissen?

Alles richtig gemacht! Du bist auf dem richtigen Weg. Mit diesem Skript möchte ich Dir helfen, Excel-Funktionen korrekt einzusetzen und neue Möglichkeiten abzuwägen.

3 https://en.wikipedia.org/wiki/Workweek_and_weekend 4 https://de.wikipedia.org/wiki/Woche



1 Einleitung Die kleine Anekdote zu Beginn hat gezeigt, dass man sein Tool und die Berechnungs-grundlagen genau kennen sollte.

Und ja, das ist tatsächlich so passiert. Wie heißt es so schön im Englischen? A fool with a tool is still a fool!5 Solche Geschichten schreibt das Leben. Und ich hoffe, dass Du von meinen Erfahrungen aus 27 Jahren Excel profitieren kannst.

Dieses kleine Skript führt zum einen in die Datums- und Zeitfunktionen ein. Soll später aber auch zusätzliche Tools wie Power Query oder andere Funktionen beleuchten, welche nützlich im Zusammenhang mit Datum und Zeit verwendet werden können. Zum anderen geht es auch um Formatierungen rund um das Thema.

Seit dem Jahr 2010 führe ich einen recht erfolgreichen YouTube-Kanal6 zum Thema Excel, aber auch Windows und andere Office-Produkte. Mit über 1.250 Videos hat sich nicht nur sehr viel Wissen dort vereint, ich erhalte auch sehr viel nützliches Feedback von Leuten, die einen noch besseren Tipp haben oder eine vertiefende Frage stellen.

Noch mehr darf ich in meinen Beratungen oder in der Excel-Forensik lernen. Die vielfältigen Fragestellungen und Ausgangslagen müssen immer wieder mit dem vorhandenen Wissen kombiniert und bei Bedarf weiter recherchiert werden. Von daher ist es zwar nützlich, einige Excel-Funktionen zu kennen. Wichtiger finde ich jedoch, sich über die grundlegenden Funktionsweisen von Excel und die Aufgabenstellung bewusst zu werden.

Mit den folgenden Fragen nähere ich mich einer Herausforderung:

Frage 1: Was will ich erreichen? Was ist mein Ziel?

Frage 2: Wie sieht mein Ausgangsmaterial aus? Wie ist die Datenbasis?

Frage 3: Wie würde ich ohne Excel vorgehen? Wie ist der Rechenweg?

Frage 4: Wie kann ich den Weg von Frage 3 in Excel realisieren? Und dies natürlich übersichtlich, nachvollziehbar, performant…

Excel selbst bietet zwar eine mittlerweile recht ordentliche Hilfe zu den Funktionen, die Übersetzungsleistung von Frage 3 zu Frage 4 muss aber von Dir geleistet und kontrolliert werden. Dafür musst Du Dein System kennen und wissen, auf welchem Terrain Du Dich bewegst.

Das Skript soll Dich auf dem Weg durch dem Datumsdschungel begleiten und wird regelmäßig um neue Themen, nützliche Ideen und neue Versionen ergänzt.

Vielen Dank für Dein Interesse und viel Spaß beim Nacharbeiten Andreas Thehos, Dezember 2017

5 Freundlich übersetzt: Ein Narr mit einem Werkzeug bleibt ein Narr! 6 http://www.youtube.com/athehos und http://www.youtube.com/atExcel



2 Datumsberechnungen In vielen Anwendungsszenarien fallen Datumswerte an und ermöglichen spannende Auswertungen und weiter gehende Berechnungen.

Fragen wie

Wie viele Werktage liegen zwischen zwei Datumswerten? Welchen Umsatz haben wir im letzten Monat erzielt? Wie viele Arbeitstage hat der aktuelle Monat? Wie ist das aktuelle Alter einer Person in Jahren? Wie viele Tage noch bis Weihnachten? An welchem Wochentag wurde ich geboren?

können mit den richtigen Fragen und Funktionen gelöst werden.

Aber auch bedingte Formatierungen sind möglich, die mir automatisch z. B. Wochenenden und Feiertage hervorheben, den Wochenwechsel grafisch anzeigen oder einen Datensatz bei Zeitüberschreitung hervorheben.

Abb. 3: Jahresübersicht mit Datumsfunktionen und bedingter Formatierung

2.1 Grundlagen

Ein Datum in Excel ist eigentlich nichts anderes, als eine umformatierte Zahl. Berechnungen mit dem Datum sind in Excel wahlweise ab dem Datum 1. Januar 1900 bzw. dem 1. Januar 1904 möglich. Warum zwei unterschiedliche Datumssysteme? Und welches gilt bei Dir?

Das 1900er-System wird standardmäßig bei den Excel-Versionen für den PC verwendet, das 1904er-System findet bei Excel für den Mac Verwendung. Seit Excel for Mac 2016 wird allerdings auch das gebräuchlichere 1900er-System genutzt.7

Unter Excel für Windows kann das Datumssystem unter Datei – Optionen – Erweitert recht weit unten in der Gruppe Beim Berechnen dieser Arbeitsmappe für die jeweilige Datei geprüft werden.

7 https://support.microsoft.com/de-de/help/214330/differences-between-the-1900-and-the-1904-date-system-in-excel



Abb. 4: Datumssystem unter Excel für Windows überprüfen

! Bei Verwendung von Excel für den Mac und Excel für Windows unbedingt prüfen8, dass beim Datenaustausch dasselbe Format genutzt wird. Ansonsten muss korrigiert werden.9

Das 1900er-System ist so aufgebaut worden, dass Dateien vom damaligen Wettbewerber Lotus 1-2-3 auch unter Excel funktionieren konnten. Der Wechsel zu Excel sollte möglichst einfach sein.

Abb. 5: 1900er-Datumsformat

Der 1. Januar 1900 liegt auf der Zahl 1, jeder weitere Tag wird einfach um die Zahl eins erhöht. Demnäch läge z.b. der 24. Dezember 2017 auf Tag 43093. Ein kleiner Schönheits-fehler wurde gleich mit von Lotus 1-2-3 übernommen. Den 29. Februar 1900 – also den Tag 60 - hat es leider nicht gegeben. Aber da wir heute selten noch mit Datumswerten vor dem 1. März 1900 rechnen müssen, ist dies eher ein zu vernachlässigendes Problem. Alle Tage vom 1. Januar 1900 bis vor dem 1. März 1900 müssen einfach plus 1 genommen werden, um dieses kleine Manko auszugleichen.

8 Bei Excel 2016 für den Mac zu prüfen unter Excel – Einstellungen – Berechnung 9 Bei der Kopie von Datumswerten zwischen zwei Dateien mit unterschiedlicher Datumsbasis werden Datumswerte automatisch um 1.462 erhöht bzw. verringert



Schwerwiegender ist es schon, dass auch Datumswerte vor dem 1. Januar 1900 nicht als Datum dargestellt werden können.10 Zudem geht mit dem 1900er-Format einher, dass Uhrzeiten bzw. Zeitdifferenzen nicht einfach per Formatierung negativ dargestellt werden können. Eine korrekte Darstellung von Fehlstunden z. B. im Format -07:30 ist also hier nicht möglich.11

Somit wird hin und wieder das 1904er-Format in manchen Internetforen empfohlen. Auch hier werden Zahlen als Datum formatiert. Tag 0 ist der 1. Januar 1904, wodurch sich Abweichungen der Zahlen bei gleichen Datumswerten zwischen den beiden Formatierungs-systemen ergeben.

Abb. 6: 1904er-Datumsformat

Dadurch, dass seit Excel 2016 für den Mac auch das 1900er-Datumsformat genutzt wird, sollte sich das Problem in den nächsten Jahren langsam auswachsen.

Mit kleinen Tricks kann man auch mit Datumswerten vor dem Jahr 1900 operieren, so gleichen sich die Wochentage und Kalender alle 400 Jahre. Eine Berechnung für den 08.09.1895 bis zum 31.07.1989 entspricht damit der Berechnung vom 08.09.2295 bis zum 31.07.2389 – und letztere kann man mit Excel berechnen.12

Generell gilt es zu beachten, dass unser aktuelles Kalendersystem, der gregorianische Kalender13, erst in den Jahren 1582 in z.B. Italien, Österreich und dem katholischen Teil von Deutschland und 1612 auch in Preußen eingeführt wurde. Die Schweiz folgte sogar erst ab dem Jahr 1700.

10 Ansatz zu Datumswerten vor 1900 von Charley Kyd http://www.exceluser.com/formulas/earlydates.htm 11 Ein negativer Zeitwert kann auch per Formel simuliert werden ="-"&Text(A1;"[h]:mm") 12 Nähere Erläuterungen im Kapitel 2.8 13 https://de.wikipedia.org/wiki/Gregorianischer_Kalender



2.2 Eingabe von Datumswerten

Stark in Anlehnung an ISO 8601 können laut DIN 5008 Datumswerte im Format Jahr-Monat-Tag bzw. JJJJ-MM-TT eingegeben werden.

Also für Heilig Abend 2019 ist die Schreibweise 2019-12-24 korrekt. Für den 1. Mai 2020 müsste 2020-05-01 eingegeben werden.

Bei uns üblich und erlaubt laut DIN 5008 sind aber auch 01.05.2018 und 1. Mai 2018.

Die Standardeinstellung für Datumswerte in Excel ist im Format TT.MM.JJJJ, drücke dafür die Tastenkombination Strg + Punkt für das aktuelle Tagesdatum.

Drücke in der Zelle unterhalb eines Datums die Tastenkombination Strg + Komma. Du siehst den darüber liegenden Wert unformatiert.

Alternativ kann eine Zelle mit einem Datum auch zu einer Zahl umformatiert werden. Drücke alternativ Strg + Umschalttaste + 6 für das Standardformat oder wähle im Register Start in der Gruppe Zahl das Format Standard.

Abb. 7: Zelle wird automatisch als Datum formatiert

Auch über den Ziffernblock der Tastatur kann ein Datum eingetragen werden. Da dort der Punkt fehlt, kann auch das Geteiltzeichen / oder das Minuszeichen - für die Eingabe genutzt werden.

Wahlweise können für den 12. Mai 2018 also 12/5/18, 12-5-18, aber auch 2018/5/12 und 2018-5-12 eingegeben werden. Wird lediglich 12.5 oder 12-5 eingegeben, resultiert der 12. Mai des aktuellen Jahres. Die Eingabe von 12.5. ohne ein Jahr wird nicht als Datum erkannt und als Text interpretiert.



2.3 Formatierung von Datumswerten

Eine als Datum formatierte Zahl kann kann ganz unterschiedlich gestaltet werden. Dabei steht der Buchstabe T für die Formatierung des Tages, M für die Formatierung des Monats und J für das Jahr. Durch Wiederholung von bis zu 5 Zeichen kann zum Beispiel der Monat April mit

M als 4 MM als 04 MMM als Apr MMMM als April MMMMM als A

angezeigt werden. M zeigt maximal den Wert 12. Hinweis: Der Kleinbuchstabe m dient zur Darstellung von Minuten.

Für das Jahr gelten die Kombinationen JJ für zwei Stellen und JJJJ für vier Stellen.

Der Wochentag des 6. Dezember 2017 – ein Mittwoch - kann mit

T als 6 TT als 06 TTT als Mi TTTT als Mittwoch

angezeigt werden. T zeigt maximal den Wert 31. Es können für T und J auch die Kleinbuchstaben t und j genutzt werden.

Die Buchstaben können frei mit anderen Zeichen wie dem Komma, dem Punkt, dem Bindestrich oder Hochkomma kombiniert werden.

Abb. 8: Beispiele für die Formatierung des Datums 1. April 2018


Andreas Thehos, 2017-2019 10 / 26

Zur Änderung des Formats einfach die Zelle mit dem Datum anwählen und z. B. mit Strg + 1 oder über die rechte Maustaste und Zellen formatieren in den folgenden Dialog wechseln. Dort ins Register Zahlen und dann entweder bei Datum ein vorhandenes Format auswählen oder unter Benutzerdefiniert anpassen.

Abb. 9: Datum formatieren

Die Änderungen dann mit OK bestätigen.

2.4 Rechnen mit Datumswerten

Da es sich bei einem Datum nur um eine Zahl handelt, können auch ganz normale Plus- und Minusoperationen durchgeführt werden.

Sollte zum Beispiel auf das Rechnungsdatum 08.01.2018 ein Zahlungsziel von 21 Kalender-tagen liegen, muss man lediglich 21 Tage addieren.

Abb. 10: Feste Anzahl von Kalendertagen auf Datum addieren

Hier wird auf den Wert von Zelle C2 der feste Wert 21 addiert.


Andreas Thehos, 2017-2019 11 / 26

Auch die Differenz von zwei Datumswerten in Tagen lässt sich schnell ermitteln.

Abb. 11: Differenz in Tagen von zwei Datumswerten

Vom jüngeren Datum einfach das ältere Datum subtrahieren.

Hier musst Du allerdings prüfen, ob Du eventuell noch den Wert 1 addieren möchtest. Sollte z.B. am selben Tag geliefert werden, kommt bei der oberen Rechnung der Wert Null heraus. Je nach Betrachtung der Berechnung kannst Du ja auch fragen, über wie viele Tage sich die Lieferung erstreckt.

Im weiteren helfen mächtige Datumsfunktionen, die gewünschten Datumsberechnungen durchzuführen.

2.5 Funktion TAGE für Datumsdifferenz in ganzen Tagen

Aus Gründen der Kompatiblität mit anderen Tabellenkalkulationen kann seit Excel 2013 auch die Funktion TAGE genutzt werden. Dabei steht vorne das Zieldatum und als zweiter Para-meter ein Ausgangsdatum. TAGE liefert positive und auch negative ganzzahlige Ergebnisse. Uhrzeiten beim Datum werden also nicht berücksichtigt!

Syntax: =TAGE(Zieldatum; Ausgangsdatum)

Abb. 12: Funktion TAGE für Tagesdifferenzen


Andreas Thehos, 2017-2019 12 / 26

Alternativ: =DATUM(JAHR(C9);MONAT(C9);TAG(C9))-DATUM(JAHR(C8);MONAT(C8);TAG(C8))

2.6 Automatisch den aktuellen Zeitpunkt ermitteln

Zwei Funktionen können genutzt werden, um Excel automatisch das aktuelle Datum bzw. auch zusätzlich die aktuelle Uhrzeit widergeben zu lassen.

Die Funktion =HEUTE() gibt automatisiert das jeweilige aktuelle Tagesdatum zurück. Anders als Strg + Punkt aktualisiert sich der Wert automatisch am nächsten Tag, ist also immer tagesaktuell. Das ist gut und schlecht zugleich.

Gut, weil man mit der Funktion =HEUTE() immer z. B. aktuelle Restlaufzeiten oder aktuelle Alterswerte berechnen kann.

Abb. 13: Differenz in Tagen ausgehend vom aktuellen Datum (03.12.2017)

Schlecht, weil man diese Aktualisierung vielleicht gar nicht möchte. Beim Rechnungsdatum wäre es unvorteilhaft, wenn sich ein Datum je nach Datum des Aufrufs verändern würde. Zudem gehört die Funktion zu den wenigen volatilen Funktionen und wird ständig neu berechnet.14

Zusätzlich errechnet die Funktion =JETZT() die aktuelle Uhrzeit. Der Wert wird automatisch aktualisiert, wenn z. B. die Taste F9 gedrückt wird oder z. B. eine weitere Eingabe irgendwo im Tabellenblatt erfolgt. Auch =JETZT() ist volatil und wird permanent neu berechnet.

Abb. 14: Differenz auf die Sekunde mit JETZT errechnet

14 Mehr zu volatilen Funktionen im Video https://www.youtube.com/watch?v=S08GFEgenb0


Andreas Thehos, 2017-2019 13 / 26

2.7 Funktionen JAHR, MONAT und TAG

Die drei Basisfunktionen JAHR, MONAT und TAG ermitteln einfach die entsprechende Zahl des Jahres, des Monats oder des Tages aus einem gegebenen Datum heraus. Das Ergebnis der Funktionen ist also immer eine Zahl bzw. eine Fehlermeldung.

Syntax: =JAHR(Datum) liefert Zahlenwerte von 1900 bis 9999

=MONAT(Datum) liefert Zahlenwerte von 1 bis 12

=Tag(Datum) liefert Zahlenwerte von 1 bis 31

Abb. 15: Funktionen JAHR, MONAT und TAG

2.8 Funktion DATUM – Datumswerte „zusammensetzen“

Die Funktion DATUM erlaubt es, einen Datumswert aus einem Jahr, einer Monatszahl und einem Kalendertag zusammenzubauen.

Syntax: =DATUM(Jahr;Monat;Tag)

Dabei kann auch bei den drei Parametern eine Formel bzw. eine Funktion eingesetzt werden.

Abb. 16: Funktion DATUM

Statt des Verweises auf eine Zelle, können auch feste Werte eingetragen werden.


Andreas Thehos, 2017-2019 14 / 26

Abb. 17: Monatsanfang mit DATUM

So kann durch die feste Eingabe eines Wertes der Wert fix gesetzt werden. Wenn bei Monaten oder Tagen der Zahlenwert über der natürlichen Grenze liegt, wird automatisch die nächst höhere Ebene erhöht. Werden die Tage im Januar z. B. über 31 erhöht, wird in den nächsten Monat hinein gerechnet. Werden die Monate über Dezember hinaus gerechnet, wechselt Excel ins Folgejahr.

Möchte man z.B. von einem Startdatum aus 15 Monate in die Zukunft schauen, wird einfach der Monat zusätzlich um 15 erhöht.

Abb. 18: Berechnung in DATUM

Und so kann ich auch die Lebensspanne meines Urgroßonkels und Mentors Joe ausrechnen, indem einfach Geburts- und Sterbetag um 400 Jahre erhöht werden.15

Üblicher sind vermutlich die Altersberechnung von Gebäuden, Unternehmen oder historischen Ereignissen.

15 Beachten, dass der gregorianische Kalender zu unterschiedlichen Zeitpunkten in den einzelnen Ländern ab 1582 eingeführt wurde


Andreas Thehos, 2017-2019 15 / 26

Abb. 19: Rechnen mit Daten vor 1900

Beim angepassten Sterbedatum in Zelle C10 nehme ich das Datum aus Zelle D5 und zerlege es mit JAHR, MONAT und TAG. Nur das Jahr wird um den Wert 400 erhöht. Anschließend setzt die Funktion DATUM wieder alles zu einem Datum zusammen.

Üblicher sind vermutlich die Altersberechnung von Gebäuden, Unternehmen oder historischen Ereignissen. In den 1980er Jahren gab es natürlich noch viel mehr Personen, die vor 1900 geboren wurden. Das Problem wächst sich also langsam aus.

Die Funktion DATEDIF wird im nachfolgenden Abschnitt erläutert.


Andreas Thehos, 2017-2019 16 / 26

2.9 Funktion DATEDIF – Datumsdifferenzen in Jahren, Monaten und Tagen

Die Funktion DATEDIF hat es in sich! Ist aber leider nur stiller Begleiter16 von Excel und lässt sich nicht während der Eingabe der Funktion oder in der Auswahl der Datums- und Zeitfunktionen anwählen.

Syntax: =DATEDIF(frühes_Datum;spätes_Datum;Einheit)

Die Differenz kann von einem Startdatum bis zu einem späteren Zieldatum angezeigt werden. Dabei stehen sechs Optionen zur Verfügung, die mit englischsprachigen Kürzeln gewählt werden müssen.

Einheit Bedeutung

Y Steht für Years, also für volle Jahre

M Steht für Month, also für volle Monate

D Steht für Days, also Tage

YM Zeigt die vollen Monate an, die nach Abzug der ganzen Jahre übrig bleiben

YD Zeigt die Tage an, die nach Abzug der ganzen Jahre übrig bleiben

MD Zeigt die Tage an, die nach Abzug aller ganzen Monate übrig bleiben

Die Kürzel müssen dafür in Anführungszeichen gesetzt werden.

Abb. 20: Funktion DATEDIF

16 Die Funktion war beim früheren Konkurrenzprodukt Lotus 1-2-3 verfügbar und ist aus Kompatibilitätsgründen Bestandteil von Excel


Andreas Thehos, 2017-2019 17 / 26

Bei der Berechnung der einzelnen Tage, kann man auch auf die beideren unteren Alternativen zurückgreifen. Bei Monaten und Jahren macht die Funktion mit den Einheitsparametern "Y" und "YM" eine gute Figur.

! Die Funktion DATEDIF steht unter Excel 2003 erst nach Aktivierung des Add-Ins Analyse-Funktionen bzw. Analysis-ToolPak zur Verfügung. Ansonsten wird nicht das Formelergebnis, sondern die Fehlermeldung #Name! angezeigt.

Per Formel könnte man aus den Angaben auch einen schönen Satz bilden:

="Der Kollege ist heute genau "&DATEDIF(C4;heute();"Y")&" Jahre, " &DATEDIF(C4;heute();"YM")&" Monate und "&DATEDIF(C4;heute();"MD")&" Tage alt."

Ergebnis:

Der Kollege ist heute genau 47 Jahre, 3 Monate und 10 Tage alt.

2.10 Unterschiede zwischen TAGE, einfacher Formel und DATEDIF

DATEDIF mit der Einheit "d" und TAGE arbeiten ähnlich mit der Ausnahme, dass TAGE auch negative Ergebnisse liefern kann. Beide vernachlässigen die Information der Uhrzeit und gucken nur auf das Datum! Währen die Verrechnung per einfacher Subtraktion auch die beim Datum hinterlegten Uhrzeiten berücksichtigt.

Abb. 21: TAGE und DATEDIF zeigen nur den Datumswechsel

Interessiert also nur der Datumswechsel, ist man mit DATEDIF und TAGE gut bediet. Die Formeln in D9 und D16 zeigen auch den Bruchteil der Uhrzeit an.


Andreas Thehos, 2017-2019 18 / 26

2.11 Kalenderwoche ermitteln – KALENDERWOCHE und ISOKALENDERWOCHE

Die Funktionen KALENDERWOCHE und ISOKALENDERWOCHE wurden im Eingangsbeispiel angesprochen. Beide Funktionen liefern eine Zahl zwischen 1 und 53, wobei im Vorfeld darauf zu achten ist, welches Datumssystem berücksichtigt werden muss.

Die Funktion KALENDERWOCHE bietet seit Excel 2010 auch den Parametertyp 21 für unser europäisches System 2.

Syntax: =KALENDERWOCHE(Datum;Typ)

Bei den Typen sind die folgenden Parameter möglich:

1 System 1, Wochenanfang montags (Standard, falls der Paramter nicht genannt wird) 2 System 1, Wochenanfang dienstags 11 System 1, Wochenanfang montags 12 System 1, Wochenanfang dienstags 13 System 1, Wochenanfang mittwochs 14 System 1, Wochenanfang donnerstags 15 System 1, Wochenanfang freitags 16 System 1, Wochenanfang samstags 17 System 1, Wochenanfang sonntags 21 System 2, Wochenanfang montags (ab Excel 2010)

Die Funktion ISOKALENDERWOCHE ist automatisch im System 2, steht aber erst mit Excel 2013 zur Verfügung.

Syntax: =ISOKALENDERWOCHE(Datum)

Das nachfolgende Beispiel zeigt allerdings, dass Kalenderwochen im System 2 innerhalb eines Kalenderjahres doppelt vorkommen können. So können die letzten drei Tage eines Jahres schon in die Kalenderwoche 1 des Folgejahres fallen bzw. die ersten drei Tage des Januars können auch in der letzten Woche des Vorjahres liegen (siehe Abb. 1).

Abb. 22: Kalenderwochen können sind regelmäßig uneindeutig


Andreas Thehos, 2017-2019 19 / 26

Durch die Berechnung in einer zusätzlichen Spalte, kann die jeweilige Kombination aus der Jahreszahl und der Kalenderwoche für Eindeutigkeit sorgen17.

Ich empfehle die Darstellung im Format Jahr-Kalenderwoche JJJJ-KW, da dies auch für einheitliche Möglichkeiten der Sortierung sorgt.

Abb. 23: Kalenderwoche 1 im Jahr 2019 doppelt vorhanden

Die folgende Formel prüft Monat und Kalenderwoche des Datums und korrigiert die zur Kalenderwoche passende Jahreszahl.

=JAHR([@Datum])- WENN(UND(KALENDERWOCHE([@Datum];21)>51;MONAT([@Datum])=1);1; WENN(UND(KALENDERWOCHE([@Datum];21)=1; MONAT([@Datum])=12);-1;0))& "-"&TEXT(KALENDERWOCHE([@Datum];21);"00")

Ein kurzer Versuch, die Logik in der Formel zu erläutern:

Von der Jahreszahl wir nur dann der Wert 1 abgezogen, wenn das Datum in einem Januar liegt, die Kalenderwoche aber einen Wert größer als 51 hat.

Alternativ wird die Jahreszahl nur dann um den Wert 1 erhöht, wenn es sich um den Dezember handelt, die Kalenderwoche aber den Wert 1 hat.

Wenn dann die Jahreszahl ausgerechnet wurde, wird per Funktion TEXT die Kalenderwoche im zweistelligen Format hinten an die Jahreszahl hinzugefügt.

17 Diese Lösung wird bei YouTube erläutert: https://youtu.be/l59ygyeGXiE


Andreas Thehos, 2017-2019 20 / 26

2.12 Texte in Datum konvertieren mit DATWERT

Sollten Datumswerte aus einem Drittsystem als Text geliefert18 und importiert werden und nicht als Zahlenwert, kann der Textwert mit der Funktion DATWERT in ein Datum konvertiert werden.

Zunächst wird in Spalte C die reine Zahl geliefert, die man natürlich auch sofort als Datum formatieren kann.

Syntax: =DATWERT(Text)

Abb. 24: DATWERT korrigiert als Text angelieferte Datumswerte

DATWERT erkennt hier auch ungültige Datumsformate und liefert die Fehlermeldung #WERT!.

Als zusätzlicher Lösungsweg kann der Textwert auch einfach mit dem Zahlwert 1 multipliziert werden.

So werden auch Jahreszahlen und Monate in der Form 2019-02 als 1. Februar 2019 interpretiert. Es muss lediglich noch die Textkette -1 für den Monatsbeginn hinzugefügt werden.

Abb. 25: Auch Monatsangaben können mit DATWERT in Datumswerte gewandelt werden

18 Dieser Fall passiert regelmäßig und betrifft häufig auch reguläre Zahlenwerte.


Andreas Thehos, 2017-2019 21 / 26

2.13 Das Ende eines Monats errechnen – MONATSENDE

Die Funktion MONATSENDE errechnet das Datum des letzten Tag eines gewünschten Monats. Dies muss nicht automatisch das Monatsende des angegebenen Datums sein.

Syntax: =MONATSENDE(Datum; x Monate weiter)

Der zweite Parameter bestimmt, ob das Monatsende des aktuellen Monats, eines vorhergehenden Monats oder nachfolgenden Monats bestimmt werden soll.

-1 Ermittelt das Monatsende des Vormonats -4 Ermittelt das Monatsende von vier Monaten vorher 0 Errechnet das Monatsende des angegebenen Datums 1 Ermittelt das Monatsende des Folgemonats 5 Errechnet das Monatsende des Monats fünf Monate weiter

So lässt sich zum Beispiel das Datum für folgende Fragestellung klären: „Auftrag klären bis zum letzten Tag des Folgemonats. Wie lautet das Datum?“.

Abb. 26: Das Monatsende ausgehend von einem Startdatum festlegen

So können aber auch die Arbeitstage eines beliebigen Monats ausgerechnet werden, vom ersten Tag, der ja immer mit eins startet, bis zum letzten Tag…

2.14 Arbeitstage ermitteln – NETTOARBEITSTAGE und NETTOARBEITSTAGE.INTL

… und das abzüglich Ferienzeiten und Wochenenden. Dafür stehen die Funktionen NETTOARBEITSTAGE und seit Excel 2010 auch NETTOARBEITSTAGE.INTL zur Verfügung.

Abb. 27: NETTOARBEITSTAGE ignoriert Wochenenden und Feiertage


Andreas Thehos, 2017-2019 22 / 26

3 In eigener Sache Seit Juni 2010 führe ich den YouTube-Kanal www.youtube.com/athehos, der sich nicht nur mit dem Thema Tabellenkalkulation, sondern auch um andere Softwareprodukte dreht.

Zum Thema Datum und Zeit habe ich schon recht viele Videos aufgezeichnet, in denen unterschiedliche Szenarien wie Stundenzettel, Jahresplaner, Projektkalender & Co. auch komplexere Themen beleuchtet wurden.

Das mein Kanal zum erfolgreichsten deutschsprachigen Excel-Kanal werden konnte und im weltweiten Vergleich auch in Konkurrenz zu den englischsprachigen Kanälen zu den Top 5 bis Top 10 gehört, habe ich neben den vielen Abonnenten natürlich den vielen Unterstützern zu verdanken.

Ständige Inspiration ist mir auch der Excel-Stammtisch, der mittlerweile auch Ableger in München und Basel hat. Regelmäßige Stammtische finden auch wieder in Essen und Köln statt. Die Termine werden regelmäßig in meinem Blog genannt.

Unter anderem bin ich unter den folgenden Webadressen im Netz vertreten:

www.at-training.de – Geschäftliche Webseite at IT-Training & Beratung, Andreas Thehos

thehosblog.com – Blogbeiträge zum Kanal, zum Excel-Stammtisch und zu Aktionen

www.yotube.com/athehos - Mehr als 1.250 Video-Tutorials zu Excel & mehr

www.youtube.com/atExcel - weitere Excel-Tutorials

Ansonsten bin ich mit meinem Profil noch bei LinkedIn online.

Ich bitte zu berücksichtigen, dass ich keinen kostenlosen Support anbiete. Bei Interesse an einer geschäftlichen Zusammenarbeit bitte eine E-Mail mit geschäftlicher Signatur an [email protected] senden.

Ich wünsche erst einmal viel Erfolg mit Excel und vielleicht bis zum nächsten Excel-Stammtisch!


Andreas Thehos, 2017-2019 23 / 26

4 Videolinks Die folgenden meiner Videos beschäftigen sich mit dem Thema Datums- und Zeitfunktionen in Excel. Die letzte Spalte zeigt den Speicherort von Video und Datei auf dem Excel-Stick an. Es sind der Vollständigkeit halber auch Videos aus zusammenhängenden Reihen aufgeführt, die nicht das Thema Datum primär zum Ziel haben.

Inhalt Link Nr

Berechnungen mit Datumswerten https://youtu.be/3IUvU4WsKgo

Funktion KALENDERWOCHE https://youtu.be/bkmCprjPLmA

Kalenderwoche inklusive Jahr anzeigen https://youtu.be/l59ygyeGXiE 863

Funktionen WOCHENTAG und ZÄHLENWENN Bedingter Kalender

https://youtu.be/_VaRR6wPE7k

Funktionen WOCHENTAG und TEXT Wochentage aus Datum ermitteln

https://youtu.be/Ex84Rgk0iAM

Funktion TAGE – Differenz in Kalendertagen https://youtu.be/JcI86o2tp9M 845

Funktion DATEDIF – Ganze Jahre, Monate und Tage https://youtu.be/4g8mE6P0ONw

Grundlagen der Stundenberechnung https://youtu.be/t509N9QwBjs

Funktion NETTOARBEITSTAGE und NETTOARBEITSTAGE.INTL – Anzahl von Arbeitstagen

https://youtu.be/ErdacwiNi9M

Dauer in Minuten angeben https://youtu.be/Oq-zoqZFuA0

Dauern von 24 Stunden und mehr anzeigen https://youtu.be/3HLjBlyj2JE

Dauer in Tagen, Stunden und Minuten anzeigen https://youtu.be/CR3ZIScOIqQ

Funktionen ZEIT, STUNDE, MINUTE, SEKUNDE Zeitfunktionen und Summe als Arrayfunktion

https://youtu.be/_3idhJxuoMg

Funktionen ARBEITSTAG und ARBEITSTAG.INTL Zieldatum unter Berücksichtigung von Feiertagen ermitteln

https://youtu.be/NHJDKokYDak

Stundenzettel 1 – Werktage eines Monats ermitteln https://youtu.be/kObWUOoI6wc 604

Stundenzettel 2 – Werktage per Kontrollkästchen wählen https://youtu.be/kObWUOoI6wc 605

Stundenzettel 3 – Tage formatieren, Wochenwechsel https://youtu.be/bFEmzlZ1J60 606

Stundenzettel 4 – Arbeitszeit ermitteln und Pausen abziehen

https://youtu.be/Dbo0d56bllE 607

Stundenzettel 5 – Gesamtstunden und Entgelt ermitteln https://youtu.be/uL69YgFgD10 608

Stundenzettel 6 – Ausdruck gestalten https://youtu.be/FvnNEhYUkm8 609

Gantt-Diagramm mit Meilensteinen und Ressourcen https://youtu.be/vsgI2VOVGuQ

Werktage der Folgewoche ermitteln https://youtu.be/kObWUOoI6wc

Jahresübersicht – Kalender erstellen - Vorschau https://youtu.be/6Ou1SD6dAkg


Andreas Thehos, 2017-2019 24 / 26

Inhalt Link Nr

Dynamischer Jahresplaner mit Feiertagen unnd anderen Ereignissen

Projektkalender 1 – Feiertage aus Outlook übernehmen https://youtu.be/ABMvbA4RadM

Projektkalender 2 – Arbeitstage auswählen https://youtu.be/bvwCsOW8te4

Projektkalender 3 – Kalender dynamisch aufbauen https://youtu.be/X3EBCXgpTY8

Projektkalender 4 – Bedingte Formatierung 1 https://youtu.be/fWODm5wPydM

Projektkalender 5 – Bedingte Formatierung 2 https://youtu.be/mronPMZiHD0

Projektkalender 6 – Blattschutz und Ausblenden – VBA https://youtu.be/_8N5fBiz84M

Projektkalender 7 – Bedingte Formatierung 3 https://youtu.be/K5x4shLNsts

Projektkalender 8 – Bedingte Formatierung 4 https://youtu.be/CscxikTm0qU

Jahresübersicht – Kalender erstellen 1 https://youtu.be/O9nz1yVqeLA

Jahresübersicht – Kalender erstellen 2 https://youtu.be/mV4QNy1IXdM

Jahresübersicht – Kalender erstellen – Feiertage per Power Query importieren

https://youtu.be/wq3stFb4WZ4

Jahresübersicht – Kalender erstellen – mehrere Einträge pro Tag ab Excel 2016

https://youtu.be/lZDEAyU_R-Q

Jahresübersicht – Kalender erstellen 3 https://youtu.be/788TzG7q-RQ

Jahresübersicht – Kalender erstellen 4 https://youtu.be/fp7o8RVNcUA

Terminplaner mit fortlaufenden Zeitintervallen https://youtu.be/e2nxOiE3q6M

Dynamische Zeitleiste 1 – Liniendiagramm https://youtu.be/_0y_neB3DcY

Dynamische Zeitleiste 2 – Liniendiagramm https://youtu.be/mwYvg_CkJXk

Dynamische Zeitleiste 3 – Liniendiagramm https://youtu.be/elbuthtN5ts

Dynamische Zeitleiste 4 – XY-Punktdiagramm https://youtu.be/MYAk1vildo8

Dynamische Zeitleiste 5 – Drehfeld-Schalter https://youtu.be/cYioEyp_mKs

Gantt 1 – Terminplan erstellen https://youtu.be/DGxL2ynLexw

Gantt 2 – Terminplan visualisieren https://youtu.be/AAJVFe7psec

Gantt 3 – Fortschritte darstellen https://youtu.be/Hgc-XV6wUgg

Gantt 4 – Meilensteine https://youtu.be/8iRsIuj2sQM

Gantt 5 – Terminplan drucken https://youtu.be/URGqwLDvA3s

Gantt 6 – Vorgänge verbinden https://youtu.be/OD8Qx1l_6bU

Gantt 7 – Kosten nach Fälligkeit https://youtu.be/tJvYVOSXg_k

Gantt 8 – Kosten mit Pivot auswerten https://youtu.be/biXgmFsGyQE

Gantt 9 – Interaktive Analyse mit Power View Excel 2013 https://youtu.be/vZa1XLkyX9k


Andreas Thehos, 2017-2019 25 / 26

Inhalt Link Nr

Gantt 10 – Daten ins Datenmodell mit Power Query übertragen

https://youtu.be/_bCzMnXUNOM

Gantt 11 – Daten mit PowerPivot anreichern https://youtu.be/40-7-p9uwVU

Gantt 12 – Daten interaktiv präsentieren https://youtu.be/czVTPaIlRtc

Unixzeit in Excel darstellen Die verstrichenen Sekunden seit dem 01.01.1970 00:00

https://youtu.be/3xL2dowqoTs

Zweifelhafte Genauigkeit bei Berechnungen und AutoAusfüllen wegen Fließkommafehler

https://youtu.be/wlsI4rnLV7o

Arbeitszeiten errechnen und Pausen abziehen Arbeitet SVERWEIS hier fehlerhaft?

https://youtu.be/gmdyXupYT6Q

Arbeitszeiten errechnen und Pausen abziehen WENN-Funktion

https://youtu.be/-wVDONELFIw

PowerPivot # 2 – DAX-Datums- und Zeitfunktionen https://youtu.be/3ciZHKfScSQ

PowerPivot # 21 – Kalender erstellen https://youtu.be/FXf0wI4RQfs

PowerPivot # 24 – Zeitachse und Datenschnitt https://youtu.be/cW1oV1dS-Ro


Andreas Thehos, 2017-2019 26 / 26

5 Abbildungsverzeichnis Abb. 1: Kalenderwoche nach DIN EN 28601 .............................................................................. 2 Abb. 2: Kalenderwoche US vs. ISO ............................................................................................. 3 Abb. 3: Jahresübersicht mit Datumsfunktionen und bedingter Formatierung ......................... 5 Abb. 4: Datumssystem unter Excel für Windows überprüfen ................................................... 6 Abb. 5: 1900er-Datumsformat ................................................................................................... 6 Abb. 6: 1904er-Datumsformat ................................................................................................... 7 Abb. 7: Zelle wird automatisch als Datum formatiert ................................................................ 8 Abb. 8: Beispiele für die Formatierung des Datums 1. April 2018 ............................................. 9 Abb. 9: Datum formatieren ...................................................................................................... 10 Abb. 10: Feste Anzahl von Kalendertagen auf Datum addieren .............................................. 10 Abb. 11: Differenz in Tagen von zwei Datumswerten .............................................................. 11 Abb. 12: Funktion TAGE für Tagesdifferenzen ......................................................................... 11 Abb. 13: Differenz in Tagen ausgehend vom aktuellen Datum (03.12.2017) .......................... 12 Abb. 14: Differenz auf die Sekunde mit JETZT errechnet......................................................... 12 Abb. 15: Funktionen JAHR, MONAT und TAG .......................................................................... 13 Abb. 16: Funktion DATUM ....................................................................................................... 13 Abb. 17: Monatsanfang mit DATUM ........................................................................................ 14 Abb. 18: Berechnung in DATUM .............................................................................................. 14 Abb. 19: Rechnen mit Daten vor 1900 ..................................................................................... 15 Abb. 20: Funktion DATEDIF ...................................................................................................... 16 Abb. 21: TAGE und DATEDIF zeigen nur den Datumswechsel ................................................. 17 Abb. 22: Kalenderwochen können sind regelmäßig uneindeutig ............................................ 18 Abb. 23: Kalenderwoche 1 im Jahr 2019 doppelt vorhanden .................................................. 19 Abb. 24: DATWERT korrigiert als Text angelieferte Datumswerte .......................................... 20 Abb. 25: Auch Monatsangaben können mit DATWERT in Datumswerte gewandelt werden . 20 Abb. 26: Das Monatsende ausgehend von einem Startdatum festlegen ................................ 21 Abb. 27: NETTOARBEITSTAGE ignoriert Wochenenden und Feiertage ................................... 21

Datums- und Zeitfunktionen - WordPress.com · 2019-03-01 · Excel – Datums- und Zeitfunktionen...

Documents

Transcript of Datums- und Zeitfunktionen - WordPress.com · 2019-03-01 · Excel – Datums- und Zeitfunktionen...