Alter berechnen in Excel

Eine auf den ersten Blick ganz simple Aufgabenstellung hat sicher schon den ein oder anderen frustriert. Es geht um die genaue Berechnung des Alters in Excel. Sollten Sie gerade auch genau zu diesem Thema im Internet recherchiert haben, wird es Sie vielleicht trösten, dass selbst der Beitrag im offiziellen Microsoft Support fehlerhafte Lösungsvorschläge anbietet. Zum Staunen und Schmunzeln habe ich den Beitrag verlinkt, allerdings sollten Sie die angebotenen Lösungsansätze getrost ignorieren. 

Exakte Berechnungen mit der DATEDIF-Funktion

Die beste Funktion um das exakte Alter zu berechnen taucht im oben genannten Beitrag gar nicht auf. Dabei können Sie mit der Funktion DATEDIF() sehr einfach die Differenz zwischen zwei Datumswerten berechnen. Die Funktion ist auch sonst sehr versteckt und taucht weder im Funktionsassistenten noch im Register Formeln auf. Und genau aus diesem Grund möchte ich Ihnen die Funktion und ihre Einsatzmöglichkeiten in diesem Beitrag vorstellen. Die Funktion ist wie folgt aufgebaut:

=DATEDIF(Startdatum;Enddatum;“Zeiteinheit“)

Die Funktion DATEDIF() setzt sich aus drei Funktionsargumenten zusammen. Das Startdatum wird in unserem Beispiel das Geburtsdatum sein. Für das Enddatum nehmen wir das aktuelle Tagesdatum. Hierfür verwenden wir die Funktion HEUTE() und erhalten so ein dynamisches Datum, dass sich täglich ändert. Alternativ kann natürlich auch ein bestimmter Stichtag als statisches Datum dienen. Zum Schluss wird noch eine Zeiteinheit benötigt. Im ersten Schritt soll das Alter in Jahren berechnet werden. Die Zeiteinheit hierfür lautet „Y“ (Years). Alternativ stehen noch diese weiteren Zeiteinheiten zur Verfügung:

Einheit Rückgabewert
„Y“ Anzahl der vollendeten Jahre zwischen den beiden Datumswerten
„M“ Anzahl der vollendeten Monate zwischen den beiden Datumswerten
„D“ Anzahl der vollendeten Tage zwischen den beiden Datumswerten
„MD“ Differenz zwischen den Tagen in Startdatum und Enddatum. Monate und Jahre werden ignoriert.
„YM“ Differenz zwischen den Monaten in Startdatum und Enddatum. Tage und Jahre werden ignoriert.
„YD“ Differenz zwischen den Tagen in Startdatum und Enddatum. Jahre werden ignoriert.

DATEDIF-Funktion an praktischen Beispielen

In der Beispieldatei stehen die Geburtstage in Spalte B und dienen für die DATEDIF()-Funktion als Startdatum. Um einige mögliche Einsatzmöglichkeiten zu demonstrieren wird die Funktion genutzt um vier unterschiedliche Fragestellungen zu lösen. In Spalte C wird das Alter in Jahren, in Spalte D der nächste Geburtstag, in Spalte E der nächste Geburtstag in Tagen und in Spalte F der nächste Geburtstag in Monaten und Tagen berechnet.

Ausgehend vom heutigen Tagesdatum wird das Alter in Jahren berechnet. Hierfür reicht die Kombination der beiden Funktionen DATEDIF() und HEUTE().

=DATEDIF(B2;HEUTE();"Y")

Bei der Ermittlung des nächsten Geburtstag werden noch weitere Funktionen benötigt. Hierfür werden zusätzlich noch die Datumsfunktionen DATUM(), JAHR(), MONAT() und TAG() genutzt.

=DATUM(JAHR(B2)+DATEDIF(B2;HEUTE();"Y")+1;MONAT(B2);TAG(B2))

Das Ergebnis dieser Formel wird dann schließlich für die Ermittlung der Tage, beziehungsweite Monate und Tage bis zum nächsten Geburtstag in den Spalten E und F als Enddatum verwendet.

=DATEDIF(HEUTE();D2;"D")
=DATEDIF(HEUTE();D2;"YM")&" Monaten, "&DATEDIF(HEUTE();D2;"MD")&" Tagen"

Excel Mustermappe:

Genaues Alter mit Excel berechnenalter_berechnen.xlsx

 

Wissenswert: Sverweis Funktion und Makro Alternative

Die Sverweis Formel-Funktion ist eine der beliebtesten Excel Funktionen überhaupt. Die Nutzung kann allerdings auch Probleme bereiten und die Performance bei großen Tabellen stark beeinträchtigen. In diesem Beitrag erfahren Sie, wofür Sie die Sverweis-Funktion nutzen können, worauf Sie achten müssen und schließlich was alternativ Makros leisten können. Dazu stelle ich Ihnen zwei Makros vor. Das erste Makro nutzt die Sverweis-Tabellenblatt-Funktion vlookup() in VBA und bietet somit alle Möglichkeiten der Formel-Funktion. Das zweite Makro stellt die Funktionsweise in ähnlicher Weise nach und bietet im Praxiseinsatz noch weitere Möglichkeiten, die mit der Sverweis-Funktion sonst nicht zu realisieren sind.

Sverweis-Formel im Tabellenblatt
Mit der Sverweis-Funktion können Sie grundsätzlich fehlende Daten aus einer anderen Tabelle zuspielen. Haben Sie zum Beispiel eine Liste mit Postleitzahlen bei denen die Orte fehlen können Sie über einen Sverweis in einer zusätzlichen Spalte die Orte in Abhänigkeit zur Postleitzahl hinzufügen. Voraussetzung ist, dass Sie über ein Postleitzahlenverzeichnis verfügen in denen zu allen Postleitzahlen die Orte gespeichert sind. Sie benötigen also eine Datenbasis aus der Sie die fehlenden Werte zuspielen können. Es sind dabei allerdings noch drei Punkte unbedingt zu beachten.

Weiterlesen

 

Straße und Hausnummer mit Excel trennen Teil 1 (Variante ohne Makro)

Ein häufiges Problem ist die Übernahme von Daten, die nicht in der gewünschten Datenqualität vorliegen. Im folgenden Beitrag stelle ich eine Möglichkeit vor, wie Sie die in einer Spalte vorliegende Anschrift bestehend aus Straße und Hausnummer mit Excel trennen können und in zwei Spalten übernehmen. Die Lösung dieser Problematik ist sogar ganz ohne den Einsatz eines VBA Makros mit den Standard-Funktionen in Excel möglich.

Teil 1: Straße und Hausnummer mit Excel trennen – Variante ohne Makro
Teil 2: Straße und Hausnummer mit Excel trennen – Variante mit Makro

Vorbereitung und Datenqualität
Ich gehe für den nachfolgenden Lösungsweg davon aus, dass die Anschriften bestehend aus Straßenname und Hausnummer untereinander in Spalte A stehen. Die Spalten B bis F werden im folgenden für Hilfsspalten und die Ergebnisse benötigt, sollten daher vollständig leer sein. Am Ende des Beitrags steht eine Beispieldatei mit den Formeln zum Download als Muster bereit.

Straße Hausnummer trennen mit Excel

Insgesamt benötigen Sie 5 Formeln um ans Ziel zu kommen. Eine Besonderheit gibt es am Ende noch für Sonderformen der Hausnummer zu berücksichtigen. Darauf komme ich am Ende des Beitrags nochmal zurück. Ich gehe für die folgenden Schritte zunächst von der idealen Datenlage (z.B. „Hartenthaler Str. 22“ oder „An der Promenade 6“) aus. Es können mehrere Leerzeichen im Straßenname vorhanden sein und es besteht zudem immer ein Leerzeichen zur Abgrenzung der Hausnummer.

Weiterlesen

 

Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen

Wenn Sie eine Tabelle in Excel haben bei der Sie die Spaltenüberschriften an der Stelle der Zeilenüberschriften und umgekehrt haben wollen, können Sie das ganz einfach ohne großen Aufwand mit einer Excel-Standardfunktion lösen. Das geht sehr schnell und ist wesentlich einfacher, als ein entsprechendes Makro zu programmieren.

Teil 1: Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln/
Teil 2: Leere Zeilen in Exceltabellen löschen
Teil 3: Doppelte Zeilen löschen
Teil 4: Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen

Allerdings müssen Sie die Funktion erst einmal finden. Die Lösung dieses Problems ist über das Fenster „Inhalte einfügen“ und der darin enthaltenen Funktion „Transponieren“ zu finden.



Abbildung 1
Das Fenster „Inhalte einfügen“ können Sie entweder über „Bearbeiten“ in der Menüleiste oder über drücken der rechten Maustaste im Tabellenbereich auswählen.

Weiterlesen

 

Zahlenwerte richtig erkennen

Sie kennen vielleicht das Problem. Sie haben eine Tabelle erhalten, in der sich Zahlenwerte befinden mit denen nicht gerechnet werden kann. Die Ursachen sind dafür vielfältig. Entweder sind die Zellen als Text formatiert, vor oder hinter der Zahl stehen noch Leerzeichen oder die Zahl ist mit einem Punkt an der Stelle zu den Nachkommastellen getrennt.

Teil 1: Zahlenwerte richtig erkennen
Teil 2: Zahlenwerte im Format Text speichern

Insbesondere bei großen Tabellen wollen Sie natürlich nicht Zelle für Zelle bereinigen. Dieses Problem können Sie schnell mit, aber auch ohne Makro lösen.

Am einfachsten lässt sich das Problem mit dem Punkt statt Komma lösen. Hierfür markieren Sie den entsprechenden Zellbereich (In der Beispieldatei der Bereich F3:F15) und rufen über das Menü „Bearbeiten“, „Ersetzen“ auf. Geben Sie nun unter „Suchen nach:“ einen Punkt und bei „Ersetzen durch:“ ein Komma ein. Anschließend klicken Sie auf „Alle ersetzen“ und im markierten Bereich werden alle Kommas durch jeweils einen Punkt ersetzt.

Weiterlesen