Excel speichert Datums- und Uhrzeitwerte als Seriennummern im Backend.
Das bedeutet, dass Sie zwar ein Datum wie "10. Januar 2021-2022" oder "01.10.2020" in einer Zelle sehen können, aber Excel speichert dies im Backend als Zahl. Dies ist nützlich, da es Benutzern auch ermöglicht, Datum und Uhrzeit in Excel einfach hinzuzufügen/zu subtrahieren.
In Microsoft Excel für Windows wird „01 Jan 1900“ als 1 gespeichert, „02 Jan 1900“ wird als 2 gespeichert usw.
Unten haben beide Spalten die gleichen Zahlen, aber Spalte A zeigt Zahlen an und Spalte B zeigt das Datum, das durch diese Zahl dargestellt wird.
Dies ist auch der Grund, warum Sie manchmal ein Datum in einer Zelle erwarten, aber am Ende eine Zahl sehen. Ich sehe dies ständig, wenn ich Daten aus Datenbanken oder webbasierten Tools herunterlade.
Dies geschieht, wenn das Zellenformat so eingestellt ist, dass eine Zahl als Zahl anstelle eines Datums angezeigt wird.
Wie können wir also diese Seriennummern in Datumsangaben umwandeln?
Darum geht es in diesem Tutorial.
In diesem Tutorial zeige ich Ihnen zwei wirklich einfache Möglichkeiten, Seriennummern in Excel in Datumsangaben umzuwandeln
Hinweis: Excel für Windows verwendet das 1900-Datumssystem (was bedeutet, dass 1 den 01. Januar 1900 darstellen würde, während Excel für Mac das 1904-Datumssystem verwendet (wobei 1 den 01. Januar 1904 in Excel in Mac darstellen würde).Also lasst uns anfangen!
Konvertieren von Seriennummern in Datumsangaben mithilfe der Zahlenformatierung
Die einfachste Möglichkeit, eine Datumsseriennummer in ein Datum umzuwandeln, besteht darin, die Formatierung der Zellen mit diesen Zahlen zu ändern.
Einige der gebräuchlichsten Datumsformate finden Sie auf der Registerkarte Start in der Multifunktionsleiste
Lassen Sie mich Ihnen zeigen, wie das geht.
Verwenden der integrierten Datumsformatoptionen in der Multifunktionsleiste
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle diese Zahlen in Spalte A in das entsprechende Datum umwandeln, das es darstellt.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie die Zellen mit der Zahl aus, die Sie in ein Datum umwandeln möchten
- Klicken Sie auf die Registerkarte „Startseite“
- Klicken Sie in der Gruppe "Zahlen" auf das Dropdown-Menü "Zahlenformatierung".
- Wählen Sie in der Dropdown-Liste die Option "Langes Datum" oder "Kurzes Datum" aus (je nachdem, welches Format diese Zahlen haben sollen)
Das ist es!
Die obigen Schritte würden die Zahlen in das ausgewählte Datumsformat konvertieren.
Die obigen Schritte haben den Wert in der Zelle nicht geändert, nur die Art und Weise, wie er angezeigt wird.
Beachten Sie, dass Excel die kurze Datumsformatierung basierend auf der regionalen Einstellung Ihres Systems aufnimmt. Wenn Sie sich beispielsweise in den USA befinden, lautet das Datumsformat TT/MM/JJJJ und in Großbritannien wäre das Datumsformat TT/MM/JJJJ.
Dies ist zwar eine schnelle Methode zum Umwandeln von Seriennummern in Datumsangaben, weist jedoch einige Einschränkungen auf:
- Es gibt nur zwei Datumsformate - kurzes Datum und langes Datum (und das auch in einem bestimmten Format). Wenn Sie also nur den Monat und das Jahr und nicht den Tag erhalten möchten, können Sie dies mit diesen Optionen nicht tun.
- Sie können Datum und Uhrzeit nicht mit den Formatierungsoptionen in der Dropdown-Liste anzeigen. Sie können entweder das Datum oder die Uhrzeit anzeigen, aber nicht beides.
Wenn Sie also mehr Flexibilität bei der Anzeige von Datumsangaben in Excel benötigen, müssen Sie die Option Benutzerdefinierte Zahlenformatierung verwenden (die als nächstes in diesem Tutorial behandelt wird).
Wenn Sie in Excel eine Zahl in ein Datum umwandeln, sehen Sie manchmal anstelle des Datums einige Rautenzeichen (etwa #####). Dies geschieht, wenn die Spaltenbreite nicht ausreicht, um das gesamte Datum aufzunehmen. Erhöhen Sie in einem solchen Fall einfach die Breite der Spalte.Erstellen eines benutzerdefinierten Datumsformats mit dem Dialogfeld „Zahlenformatierung“
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Seriennummern in Spalte A in Datumsangaben im folgenden Format umwandeln - 01.01.2020
Beachten Sie, dass diese Option in der zuvor behandelten Menübandmethode nicht standardmäßig verfügbar ist.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie die Zellen mit der Zahl aus, die Sie in ein Datum umwandeln möchten
- Klicken Sie auf die Registerkarte „Startseite“
- Klicken Sie in der Gruppe Zahlen auf das Symbol für das Startprogramm des Dialogfelds (es ist ein kleiner geneigter Pfeil unten rechts in der Gruppe).
- Stellen Sie im sich öffnenden Dialogfeld "Zellen formatieren" sicher, dass die Registerkarte "Zahl" ausgewählt ist
- Wählen Sie in den „Kategorie“-Optionen auf der linken Seite „Datum“.
- Wählen Sie im Feld „Typ“ die gewünschte Formatierung aus.
- OK klicken
Die obigen Schritte würden die Zahlen in das ausgewählte Datumsformat konvertieren.
Wie Sie sehen, gibt es in diesem Fall mehr Optionen zur Datumsformatierung (im Vergleich zu dem kurzen und dem langen Datum, das wir mit dem Menüband erhalten haben).
Und falls Sie das gewünschte Format nicht finden, können Sie auch Ihr eigenes Datumsformat erstellen.
Angenommen, ich möchte das Datum im folgenden Format anzeigen - 01.01.2020 (was im Dialogfeld Zellen formatieren nicht bereits eine Option ist).
So kann ich das machen:
- Wählen Sie die Zellen mit den Zahlen aus, die Sie in ein Datum umwandeln möchten
- Klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Zahlen auf das Symbol für das Startprogramm des Dialogfelds (es ist ein kleiner Kipppfeil unten rechts in der Gruppe).
- Stellen Sie im sich öffnenden Dialogfeld "Zellen formatieren" sicher, dass die Registerkarte "Zahl" ausgewählt ist
- Wählen Sie in den "Kategorie"-Optionen auf der linken Seite "Benutzerdefiniert".
- Geben Sie in das Feld rechts ein
MM/TT/JJJJ
- OK klicken
Die obigen Schritte würden alle Zahlen in das angegebene Zahlenformat ändern.
Mit dem benutzerdefinierten Format erhalten Sie die volle Kontrolle und können das Datum in jedem gewünschten Format anzeigen. Sie können auch ein Format erstellen, in dem das Datum sowie die Uhrzeit in derselben Zelle angezeigt werden.
Wenn Sie beispielsweise im selben Beispiel sowohl das Datum als auch die Uhrzeit anzeigen möchten, können Sie das folgende Format verwenden:
mm/tt/jjjj hh:mm AM/PM
Nachfolgend finden Sie die Tabelle mit den Datumsformatcodes, die Sie verwenden können:
| Datumsformatierungscode | Wie es das Datum formatiert |
| m | Zeigt den Monat als Zahl von 1-12 . an |
| mm | Zeigen Sie die Monate als zweistellige Zahlen von 01-12 . an |
| mmm | Zeigt den Monat mit drei Buchstaben an, wie in Jan-Dez |
| mmmm | Zeigt den vollständigen Namen des Monats wie im Januar-Dezember an |
| mmmmm | Zeigt das erste Alphabet des Monatsnamens wie in J-D |
| D | Zeigt Tage als 1-31 . an |
| dd | Zeigt Tage als 01-31 |
| ddd | Zeigt Tage als So-Sa |
| dddd | Zeigt Tage als Sonntag-Samstag an |
| yy | Zeigt Jahre als 00-99 . an |
| yyyy | Zeigt Jahre als 1900-9999 an |
Konvertieren Sie Seriennummern in Datumsangaben mit der TEXT-Formel
Die oben beschriebenen Methoden funktionieren, indem die Formatierung der Zellen mit den Zahlen geändert wird.
In einigen Fällen können Sie diese Methoden jedoch möglicherweise nicht verwenden.
Zum Beispiel habe ich unten einen Datensatz, bei dem die Zahlen einen führenden Apostroph haben - der diese Zahlen in Text umwandelt.
Wenn Sie versuchen, die eingebauten Optionen zu verwenden, um die Formatierung der Zellen mit diesem Datensatz zu ändern, würde dies nichts bewirken. Dies liegt daran, dass Excel diese nicht als Zahlen betrachtet, und da Daten für Zahlen gelten, lehnt Excel Ihren Wunsch ab, diese zu formatieren.
In einem solchen Fall können Sie diesen Text entweder in Zahlen umwandeln und dann die oben beschriebenen Formatierungsmethoden verwenden oder eine andere Technik verwenden, um Seriennummern in Datumsangaben umzuwandeln - mit der TEXT-Funktion.
Die TEXT-Funktion benötigt zwei Argumente - die zu formatierende Zahl und den Formatcode.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle Zahlen und Spalte A in Datumsangaben umwandeln.
Unten die Formel, die das tun könnte:
=TEXT(A2,"MM/TT/JJJJ")
Beachten Sie, dass ich in der Formel die Datumsformatierung im Format MM/TT/JJJJ angegeben habe. Wenn Sie eine andere Formatierung benötigen, können Sie den Code dafür als zweites Argument der TEXT-Formel angeben.
Sie können diese Formel auch verwenden, um das Datum sowie die Uhrzeit anzuzeigen.
Wenn Sie beispielsweise möchten, dass das Endergebnis das Format - 01.01.2020 00:00 Uhr hat, verwenden Sie die folgende Formel:
=TEXT(A2,"mm/dd/yyyy hh:mm AM/PM")
In der obigen Formel habe ich auch das Zeitformat hinzugefügt. Wenn die Zahlen also Dezimalteile enthalten, wird es als Zeit in Stunden und Minuten angezeigt.
Wenn Sie nur das Datum (und nicht die zugrunde liegende Formel) benötigen, konvertieren Sie die Formeln mit Paste Special in statische Werte.
Ein großer Vorteil der Verwendung der TEXT-Funktion zum Umwandeln von Seriennummern in Datumsangaben besteht darin, dass Sie das TEXT-Formelergebnis mit anderen Formeln kombinieren können. Sie können beispielsweise das resultierende Datum mit dem Text kombinieren und etwas anzeigen wie - Das Fälligkeitsdatum ist der 01. Januar 2020 oder Einsendeschluss ist der 01. Januar 2020
Hinweis: Denken Sie daran, dass Datum und Uhrzeit als Zahlen gespeichert werden, wobei jede ganze Zahl einen ganzen Tag darstellen würde und der Dezimalteil diesen großen Teil des Tages darstellen würde. 1 würde also 1 ganzer Tag bedeuten und 0,5 würde einen halben Tag oder 12 Stunden bedeuten.
Dies sind also zwei einfache Möglichkeiten, um Seriennummern in Microsoft Excel in Datumsangaben umzuwandeln.
Ich hoffe, Sie fanden dieses Tutorial nützlich!
