Datums- und Zeitfunktionen - mein Excel-Blog · Excel – Datums- und Zeitfunktionen 2 / 22 Es war...

Datums- und Zeitfunktionen

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

Stand: 07. Januar 2018

Excel – Datums- und Zeitfunktionen

1 / 22

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

3 In eigener Sache ................................................................................................................ 18

4 Videolinks .......................................................................................................................... 19

5 Abbildungsverzeichnis ...................................................................................................... 22


2 / 22

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 den Tag Kalenderwoche 37 an, die Waren seien gerade in Hamburg.

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


3 / 22

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 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 Parameter der Funktionen.

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

? 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


4 / 22

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 25 Jahren Excel profitieren kannst.

Dieses kleine Skript führt zum einen in die Datums- und Zeitfunktionen ein. Soll 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.200 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 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


5 / 22

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


6 / 22

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


7 / 22

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 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 Formatierungssystemen 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 1582 in z.B. Italien, Österreich und dem katholischen Teil von Deutchland 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


8 / 22

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 2017 ist die Schreibweise 2017-12-24 korrekt. Für den 1. Mai 2018 müsste 2018-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.


9 / 22

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

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.


10 / 22

Abb. 9: Datum formatieren

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.

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


11 / 22

Abb. 11: Differenz in Tagen von zwei Datumswerten

Hier wird einfach vom jüngeren Datum das ältere Datum abgezogen.

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


12 / 22

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


13 / 22

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 Werte von 1900 bis 9999

=MONAT(Datum) liefert Werte von 1 bis 12

=Tag(Datum) liefert Werte 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.


14 / 22

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.

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


15 / 22

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.


16 / 22

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


17 / 22

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.


18 / 22

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 Abonennten 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 Bochum 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

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

www.facebook.com/excelthehos - Mein geschäftlicher Facebook-Auftritt

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

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

www.twitter.com/athehos - weitere Nachrichten zu meinen Aktivitäten

Ansonsten bin ich mit meinem Profil noch bei LinkedIn, XING, Facebook und Instagram.

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!


19 / 22

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

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 Dynamischer Jahresplaner mit Feiertagen unnd anderen Ereignissen

https://youtu.be/6Ou1SD6dAkg


20 / 22

Inhalt Link Nr

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

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


21 / 22

Inhalt Link Nr

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


22 / 22

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

Datums- und Zeitfunktionen - mein Excel-Blog · Excel – Datums- und Zeitfunktionen 2 / 22 Es war...

Documents

Transcript of Datums- und Zeitfunktionen - mein Excel-Blog · Excel – Datums- und Zeitfunktionen 2 / 22 Es war...