Ich habe mich noch etwas mit dem „Altersproblem“ beschäftigt und dabei eine Alternative im Netz von Imke Feldmann entdeckt:
(Startdatum as date, Enddatum as date) =>
let
StartdatumINT = Date.Year(Startdatum) * 10000 + Date.Month(Startdatum) * 100 + Date.Day(Startdatum),
EnddatumINT = Date.Year(Enddatum) * 10000 + Date.Month(Enddatum) * 100 + Date.Day(Enddatum),
Alter = Number.IntegerDivide((EnddatumINT - StartdatumINT),10000)
in Alter
Gruß
Christian
Stimmt, Christian,
diese Lösung – den Monat mit einer sehr großen Zahl und den Tag mit einer kleineren zu multiplizieren, habe ich vergessen. Die Lösung habe ich auch vor vielen Jahren mal irgendwo gefunden. Auch clever.
Ich habe gestern darauf hingewiesen, dass die Altersberechnung von Power Query (es wird die Anzahl der Tage durch 365 dividiert und damit die Schalttage übergangen) sehr unscharf ist.
Man kann durch 365,25 (geschrieben: 365.25) dividieren. Das stimmt.
Oder nicht?
Nein – leider nicht ganz. Das Jahr 2024 war ein Schaltjahr, das heiß: es gab einen 29.02.2024.
Heute ist der 11.09.2024
Die Anzahl der Tage zum 11.09.2023, 11.09.2022 und 11.09.2021 betragen 366, 731 und 1096 Tage. Teilt man diese Zahlen durch 365.25 erhält man 1,0020534 beziehungsweise 2,0013689 und 3,0006845
Abgerundet also die Zahlen 1, 2 und 3
Angenommen heute wäre der 11.09.2023 (also kein Schaltjahr). Dann beträgt die Differenz zum 11.09.2022, 11.09.2021 und 11.09.2020 als Ergebnis 0,9993155 beziehungsweise 1,9986311 und 2,9979466 – oder abgerundet:
0, 1 und 2
In den Nicht-Schaltjahren bleibt ein Tag Differenz: das bedeutet: Er oder sie wird erst „einen Tag später“ ein Jahr älter.
Wir erstellen in Power Query eine einfache Funktion:
(Anfangsdatum as date, Enddatum as date) =>
Number.RoundDown(Duration.Days(Enddatum - Anfangsdatum) / 365.25)
Randbemerkung: Leider kann man nicht Enddatum – Anfangsdatum rechnen (wie in Excel), sondern muss das Ergebnis mit Duration.Days in eine (Tages-)Zahl konvertieren.
Und so zeigt sich die Unschärfe von einem Tag:
Richtig wäre folgende Berechnung: Jahr vom Ende minus Jahr vom Anfang.
Wenn der Monat des Enddatums kleiner als der Monat des Anfangsdatums, dann muss 1 abgezogen werden.
Wenn beide Monate gleich, allerdings der Tag des Enddatums kleiner als der Tag des Anfangsdatums, dann muss 1 abgezogen werden (umgangssprachlich: er oder sie hatte noch nicht in diesem Jahr Geburtstag). Oder als Formel:
(Anfangsdatum as date, Enddatum as date) =>
Date.Year(Enddatum) - Date.Year(Anfangsdatum) -
(if Date.Month(Enddatum) < Date.Month(Anfangsdatum) then 1 else
if Date.Month(Enddatum) = Date.Month(Anfangsdatum) and
Date.Day(Enddatum) < Date.Day(Anfangsdatum) then
1 else 0)
Rechnet korrekt:
Oder – man kann auch anders rechnen. Man transformiert das Anfangsdatum ins Jahr des Enddatums. Also: man holt Tag und Monat des Anfangsdatums und Jahr des Enddatums und baut ein Datum daraus.
Man berechnet Jahr minus Jahr.
Wenn das transformierte Datum größer als das Enddatum ist, muss noch 1 abgezogen werden.
Umgangssprachlich bei Geburtstagen: sollte er oder sie in diesem Jahr noch nicht Geburtstag gehabt haben, muss man 1 abziehen. Als Formel:
(Anfangsdatum as date, Enddatum as date) =>
Date.Year(Enddatum) - Date.Year(Anfangsdatum) -
(if #date(Date.Year(Enddatum), Date.Month(Anfangsdatum), Date.Day(Anfangsdatum)) > Enddatum then
1 else 0)
Klappt auch:
Natürlich sollte man das Enddatum optional setzen, beispielsweise so:
(Anfangsdatum as date, optional Enddatum as date) =>
let
EnddatumNeu = if
Enddatum is null then
Date.From(DateTime.LocalNow()) else
Enddatum,
Diff = Date.Year(EnddatumNeu) - Date.Year(Anfangsdatum),
Alter = Diff - (if
#date(Date.Year(EnddatumNeu), Date.Month(Anfangsdatum), Date.Day(Anfangsdatum)) > EnddatumNeu then
1 else 0)
in
Alter
Oh wie schön wäre eine Funktion DateDif oder DATEDIFF!
Gestern auf dem Excelstammtisch. Angelika (Angelika Meyer; https://www.asmeyer.de/) will es wissen:
Sie hat eine Liste mit Namen und Geburtstagsdaten. Diese werden in Power Query abgerufen:
Sie möchte das Alter berechnen und dann in einer Pivottabelle gruppieren, um einen Überblick über die Altersstruktur zu erhalten.
Es erstaunt:
Excel stellt die Funktion DATEDIF zur Verfügung
VBA stellt die Funktion DateDiff zur Verfügung
DAX stellt die Funktion DATEDIFF zur Verfügung
Und Power Query? Nichts dergleichen. Also per Hand:
Über Spalte hinzufügen / Datum / Alter kann man eine berechnete Altersspalte erzeugen. Wirklich?
Das Ergebnis ist eine Dauer – genauer: die Differenz in Tagen zwischen dem aktuellen Datum und dem Geburtsdatum (hier: Spalte „Birthday“)
Im zweiten Schritt kann man über Transformieren / Dauer / Jahre gesamt diese Spalte in eine Jahreszahl verwandeln:
Das Ergebnis: Dezimalzahlen
Diese müssen abgerundet werden – hier hilft Transformieren / Runden / Abrunden:
Aber ist das Ergebnis korrekt? Ich stutze. Wir probieren es. Heute ist der 10. September. Ich trage einige Geburtsdaten ein – vom 01.09 bis zum 30.09:
Bis zum 25. September sind diese Personen 60 Jahre als. Das ist falsch. Warum?
Ein Blick in den Code hilft. Power Query berechnet das Alter:
= Table.TransformColumns(#"Eingefügtes Alter",{{"Alter", each Duration.TotalDays(_) / 365, type number}})
Power Query teilt die Dauer durch 365. Dadurch werden Schaltjahre nicht berücksichtigt. Bei einem 60jährigen macht dies eine Differenz von 60/4 = 15 Tage aus. Wir versuchen den Code anzupassen:
= Table.TransformColumns(#"Eingefügtes Alter",{{"Alter", each Duration.TotalDays(_) / 365.25, type number}})
Wir teilen durch 365.25
Das Ergebnis ist besser:
Oder man muss eine eigene Funktion für dieses Problem erstellen.
