Excel speichert kalendarische Daten, also die Zusammensetzung von Tag, Monat und Jahr, in einer Zahl ab. Ob diese Zahl dem Excel-Nutzer als Zahl oder als Datum interpretiert dargestellt wird, hängt von der Formatierung ab. Die Zahl 33 kann als 33 im Zahlenformat interpretiert und dargestellt werden, oder als Datum formatiert, zum Donnerstag, 2. Februar 1900 werden (Abbildung 1).
Abbildung 1: Die Formatierung als Datum macht aus der 33 den 2. Februar 1900.
Dass aus der Zahl 33 der 2.2.1900 wird, liegt daran, dass dieser Tag der 33. jenes Jahres ist – und für Excel der 33. Tag überhaupt. Die ersten 1900 Jahre unserer Zeitrechnung spielen in der Welt von Excel keine Rolle, wie Abbildung 2 zeigt.
Problem: Excel kennt kein Datum vor dem 1.1.1900
Abbildung 2: kalendarische Daten links als Datum formatiert, rechts als Zahl
In Abbildung 2 verrät schon die linksbündige Ausrichtung von 31.12.1899, dass Excel diesen Inhalt in der als Datum formatierten Zelle bloß als Text und nicht als Datum oder Zahl erkennt. Entsprechend funktioniert hier auch nicht die Formatierung als Zahl.
Will man berechnen wie groß die Differenz zwischen zwei Daten ist, und ausgeben, wie viele Jahre zwischen zwei Tagen liegen, nutzt man beispielsweise für Albert Speer in Abbildung 3 diese Formel: =(C7-B7)/365
Abbildung 3: Die Differenz von Daten ist einfach zu berechnen, wenn das Datum nicht vor 1900 liegt.
Diese Formel wurde in Abbildung 3 analog auch in den Zellen D2 bis D8 genutzt und führt in D2 bis D6 zu Fehlermeldungen, weil Excel keine Datum vor 1900 erkennt und daher mit ihnen auch nicht rechnen kann.
Microsofts Lösung für das amerikanische Datumsformat
Unter https://support.microsoft.com/en-gb/help/245104/how-to-calculate-ages-before-1-1-1900-in-excel bietet Microsoft eine Lösung an. Wenn man in Excel die betroffene Arbeitsmappe offen hat und mit alt + F11 den Visual Basic Editor öffnet, muss man folgenden Code für eine neue Funktion in Excel als neues Modul einfügen (Abbildung 4), speichern und für Datumsdifferenzen die Formel =AgeFunc(startdate,enddate) ausführen.
Abbildung 4: Im Visual Basic Editor muss ein neues Modul mit dem Code eingefügt werden. Nach dem Speichern und Schließen kann die neue Formel genutzt werden.
' This is the initial function. It takes in a start date and an end date.
Public Function AgeFunc(stdate As Variant, endate As Variant)
' Dim our variables.
Dim stvar As String
Dim stmon As String
Dim stday As String
Dim styr As String
Dim endvar As String
Dim endmon As String
Dim endday As String
Dim endyr As String
Dim stmonf As Integer
Dim stdayf As Integer
Dim styrf As Integer
Dim endmonf As Integer
Dim enddayf As Integer
Dim endyrf As Integer
Dim years As Integer
' This variable will be used to modify string length.
Dim fx As Integer
fx = 0
' Calls custom function sfunc which runs the Search worksheet function
' and returns the results.
' Searches for the first "/" sign in the start date.
stvar = sfunc("/", stdate)
' Parse the month and day from the start date.
stmon = Left(stdate, sfunc("/", stdate) - 1)
stday = Mid(stdate, stvar + 1, sfunc("/", stdate, sfunc("/", stdate) + 1) - stvar - 1)
' Check the length of the day and month strings and modify the string
' length variable.
If Len(stday) = 1 Then fx = fx + 1
If Len(stmon) = 2 Then fx = fx + 1
' Parse the year, using information from the string length variable.
styr = Right(stdate, Len(stdate) - (sfunc("/", stdate) + 1) - stvar + fx)
' Change the text values we obtained to integers for calculation
' purposes.
stmonf = CInt(stmon)
stdayf = CInt(stday)
styrf = CInt(styr)
' Check for valid date entries.
If stmonf < 1 Or stmonf > 12 Or stdayf < 1 Or stdayf > 31 Or styrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If
' Reset the string length variable.
fx = 0
' Parse the first "/" sign from the end date.
endvar = sfunc("/", endate)
' Parse the month and day from the end date.
endmon = Left(endate, sfunc("/", endate) - 1)
endday = Mid(endate, endvar + 1, sfunc("/", endate, sfunc("/", endate) + 1) - endvar - 1)
' Check the length of the day and month strings and modify the string
' length variable.
If Len(endday) = 1 Then fx = fx + 1
If Len(endmon) = 2 Then fx = fx + 1
' Parse the year, using information from the string length variable.
endyr = Right(endate, Len(endate) - (sfunc("/", endate) + 1) - endvar + fx)
' Change the text values we obtained to integers for calculation
' purposes.
endmonf = CInt(endmon)
enddayf = CInt(endday)
endyrf = CInt(endyr)
' Check for valid date entries.
If endmonf < 1 Or endmonf > 12 Or enddayf < 1 Or enddayf > 31 Or endyrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If
' Determine the initial number of years by subtracting the first and
' second year.
years = endyrf - styrf
' Look at the month and day values to make sure a full year has passed.
If stmonf > endmonf Then
years = years - 1
End If
If stmonf = endmonf And stdayf > enddayf Then
years = years - 1
End If
' Make sure that we are not returning a negative number and, if not,
' return the years.
If years < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = years
End If
End Function
' This is a second function that the first will call.
' It runs the Search worksheet function with arguments passed from AgeFunc.
' It is used so that the code is easier to read.
Public Function sfunc(x As Variant, y As Variant, Optional z As Variant)
sfunc = Application.WorksheetFunction.Search(x, y, z)
End Function
Benutzt man diesen Code im deutschsprachigen Excel bzw. mit deutschen Datumsformaten, führt aber auch diese Funktion zu Fehlern. Dieser Code muss etwas angepasst werden.
Anpassung für das deutsche Datumsformat
Drei Veränderungen muss man vornehmen, wenn man die Lösung nutzen möchte, die Microsoft für die Berechnung von Datumsdifferenzen mit Daten vor 1900 uns anbietet. Abbildung 5 zeigt aus dem Code von Microsoft exemplarisch zwei Unterschiede zwischen dem amerikanischen und deutschen Datumsformat. Im Kommentar (grün) wird erklärt, dass in der vierten Zeile nach dem Seperator gesucht wird. In den USA werden Daten oft so notiert: 12/31/2000. Ein Schrägstrich trennt Monat, Tag und Jahr. Hier wurde nun stattdessen der Punkt eingefügt, da im deutschen Datumsformat der Punkt das gebräuchliche Trennzeichen ist (31.12.2000). Der zweite Unterschied ist die Reihenfolge von Monat und Tag bzw. Tag und Monat. Hier wurden nun die Variablen stday und stmon für Tag und Monat getauscht, da im Deutschen links der Tag und in der Mitte der Monat steht. Diese Veränderungen mussten im Code von Microsoft einige Male vorgenommen werden.
Abbildung 5: Der Unterschied zwischen dem deutschen und US-amerikanischen Datumsformat liegt in der Anordnung von Tag und Monat und im genutzten Trennzeichen.
Die letzte Abweichung von der Anleitung von Microsoft ist folgende. Wie Abbildung 6 zeigt, soll die neue Funktion AgeFunc mit ihren zwei erwarteten Argumenten mittels eines Kommas zwischen den Argumenten aufgerufen werden. Wenigstens in der deutschsprachigen Version von Excel wird ein Semikolon dafür genutzt. Ein Komma führt zu einer Fehlermeldung.
Abbildung 6: Argumente einer Funktion werden in der deutschsprachigen Version von Excel mit einem Semikolon gentrennt.
Wenn diese drei Dinge berücksichtigt werden, kann die neue Formel genutzt werden, wie in Spalte E der Abbildung 7.
Abbildung 7: Spalte E arbeiten mit der von Microsoft gelieferten Lösung, die für das deutsche Datumsformat angepasst wurde.
Der Code, der nun die Differenz zwischen zwei Daten, auch vor 1900 liegend, für das deutsche Datumsformat berechnet, ist folgender:
' This is the initial function. It takes in a start date and an end date.
Public Function AgeFunc(stdate As Variant, endate As Variant)
' Dim our variables.
Dim stvar As String
Dim stmon As String
Dim stday As String
Dim styr As String
Dim endvar As String
Dim endmon As String
Dim endday As String
Dim endyr As String
Dim stmonf As Integer
Dim stdayf As Integer
Dim styrf As Integer
Dim endmonf As Integer
Dim enddayf As Integer
Dim endyrf As Integer
Dim years As Integer
' This variable will be used to modify string length.
Dim fx As Integer
fx = 0
' Calls custom function sfunc which runs the Search worksheet function
' and returns the results.
' Searches for the first "/" sign in the start date.
stvar = sfunc(".", stdate)
' Parse the month and day from the start date.
stday = Left(stdate, sfunc(".", stdate) - 1)
stmon = Mid(stdate, stvar + 1, sfunc(".", stdate, sfunc(".", stdate) + 1) - stvar - 1)
' Check the length of the day and month strings and modify the string
' length variable.
If Len(stday) = 1 Then fx = fx + 1
If Len(stmon) = 2 Then fx = fx + 1
' Parse the year, using information from the string length variable.
styr = Right(stdate, Len(stdate) - (sfunc(".", stdate) + 1) - stvar + fx)
' Change the text values we obtained to integers for calculation
' purposes.
stmonf = CInt(stmon)
stdayf = CInt(stday)
styrf = CInt(styr)
' Check for valid date entries.
If stmonf < 1 Or stmonf > 12 Or stdayf < 1 Or stdayf > 31 Or styrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If
' Reset the string length variable.
fx = 0
' Parse the first "/" sign from the end date.
endvar = sfunc(".", endate)
' Parse the month and day from the end date.
endday = Left(endate, sfunc(".", endate) - 1)
endmon = Mid(endate, endvar + 1, sfunc(".", endate, sfunc(".", endate) + 1) - endvar - 1)
' Check the length of the day and month strings and modify the string
' length variable.
If Len(endday) = 1 Then fx = fx + 1
If Len(endmon) = 2 Then fx = fx + 1
' Parse the year, using information from the string length variable.
endyr = Right(endate, Len(endate) - (sfunc(".", endate) + 1) - endvar + fx)
' Change the text values we obtained to integers for calculation
' purposes.
endmonf = CInt(endmon)
enddayf = CInt(endday)
endyrf = CInt(endyr)
' Check for valid date entries.
If endmonf < 1 Or endmonf > 12 Or enddayf < 1 Or enddayf > 31 Or endyrf < 1 Then
AgeFunc = "Invalid Date"
Exit Function
End If
' Determine the initial number of years by subtracting the first and
' second year.
years = endyrf - styrf
' Look at the month and day values to make sure a full year has passed.
If stmonf > endmonf Then
years = years - 1
End If
If stmonf = endmonf And stdayf > enddayf Then
years = years - 1
End If
' Make sure that we are not returning a negative number and, if not,
' return the years.
If years < 0 Then
AgeFunc = "Invalid Date"
Else
AgeFunc = years
End If
End Function
' This is a second function that the first will call.
' It runs the Search worksheet function with arguments passed from AgeFunc.
' It is used so that the code is easier to read.
Public Function sfunc(x As Variant, y As Variant, Optional z As Variant)
sfunc = Application.WorksheetFunction.Search(x, y, z)
End Function
Achtung: Diese Arbeitsmappe muss im Excelformat mit Makros gespeichert werden und hat dann die Dateiendung .xlsm und nicht .xlsx. Sonst gehen alle Änderung verloren, da die Funktion nicht mehr aufgerufen werden kann.
Die in diesem Artikel genutzte Exceldatei Differenz_deutsches_Datum_vor1900.xlsm kann hier heruntergeladen werden: Differenz_deutsches_Datum_vor1900.zip
Bilder dieses Artikels: CC-BY-SA, Affenspass.de, 2019
Keine Antworten