Zahl in VBA in Datum und Zeit umwandeln
Schritt-für-Schritt-Anleitung
Um einen UNIX-Zeitstempel in ein Excel-Datum und eine Uhrzeit umzuwandeln, kannst du die folgende VBA-Funktion verwenden. Diese Funktion konvertiert sowohl 10- als auch 13-stellige Zeitstempel und berücksichtigt dabei auch Zeitzonen:
'Function: UnixTime2XL
'Converts a UNIX Timestamp to an EXCEL DateTime value
' timeStamp = A Unix Timestamp
' GMToffset = Hours offset to GMT, if omitted NO offset
' millis = timestamp is in milliseconds (automatically assumed if timestamp > 2^32)
' trueXLDate = If TRUE, cutoff milliseconds
Public Function UnixTime2XL(TimeStamp As Variant, Optional GMToffset As Integer, Optional _
millis As Boolean, Optional trueXLDate As Boolean) As Variant
Dim timeStampSec As Double
If trueXLDate And Len(TimeStamp) > 10 Then TimeStamp = Left(TimeStamp, 10)
If Len(TimeStamp) > 10 Or millis Then
timeStampSec = CDbl(WorksheetFunction.RoundDown(TimeStamp / 1000, 0))
UnixTime2XL = (timeStampSec / 3600 / 24) + DateSerial(1970, 1, 1)
If GMToffset > 0 Then UnixTime2XL = UnixTime2XL + (1 / 24 * GMToffset * 1)
UnixTime2XL = Format(UnixTime2XL, "dd.mm.yyyy hh:mm:ss") & "." & (TimeStamp - (timeStampSec * 1000))
Else
UnixTime2XL = (TimeStamp / 3600 / 24) + DateSerial(1970, 1, 1)
If GMToffset > 0 Then UnixTime2XL = UnixTime2XL + (1 / 24 * GMToffset)
UnixTime2XL = CDate(UnixTime2XL)
End If
End Function
Um die Funktion zu verwenden, gib einfach die folgende Formel in eine Zelle ein:
=UnixTime2XL(1540234226865)
Das ergibt das Datum und die Uhrzeit für den UNIX-Zeitstempel.
Häufige Fehler und Lösungen
-
Fehler: Falsches Datum oder Uhrzeit
- Lösung: Überprüfe, ob der Zeitstempel in Millisekunden angegeben ist. In diesem Fall musst du die Funktion so anpassen, dass sie die Millisekunden richtig behandelt.
-
Fehler: #WERT! oder #NAME?
- Lösung: Stelle sicher, dass die Funktion korrekt in einem Standardmodul in VBA eingefügt wurde.
-
Fehler: Ungültige Zeitzone
- Lösung: Achte darauf, dass der GMToffset korrekt angegeben ist. Ein positiver Wert verschiebt die Zeit nach vorne, ein negativer Wert nach hinten.
Alternative Methoden
Wenn du keinen VBA-Code verwenden möchtest, kannst du auch die folgenden Excel-Funktionen nutzen:
-
TEXT-Funktion: Wenn du einen UNIX-Zeitstempel hast, kannst du ihn auch direkt mit der TEXT-Funktion umwandeln.
=TEXT((A1/86400) + DATE(1970,1,1), "dd.mm.yyyy hh:mm:ss")
Hierbei ist A1 die Zelle mit deinem UNIX-Zeitstempel in Sekunden.
-
Power Query: Importiere deine Daten in Power Query und nutze das Datumsformatierungswerkzeug, um die Zeitstempel zu konvertieren.
Praktische Beispiele
-
Beispiel zur Umwandlung eines UNIX-Zeitstempels:
Gebe in Zelle A1 den Wert 1540234226865 ein und in B1 die folgende Formel:
=UnixTime2XL(A1)
Das Ergebnis wird 22.10.2018 18:50:26.865 sein.
-
E+14 in Excel: Um zu zeigen, dass solche Zahlen in Excel sehr hoch sind und möglicherweise als wissenschaftliche Notation angezeigt werden, formatiere die Zelle als Text, bevor du den Wert eingibst.
Tipps für Profis
-
Zeitzonen berücksichtigen: Bei der Umwandlung von Zeitstempeln ist es wichtig, die korrekten GMT-Offsets zu verwenden, um die Uhrzeit präzise zu berechnen.
-
Nutzung von UDFs: Erstelle eigene benutzerdefinierte Funktionen (UDFs) in VBA, um wiederkehrende Umwandlungen zu automatisieren und Zeit zu sparen.
-
Excel-Formatierung: Stelle sicher, dass die Zellen, in denen du die Ergebnisse anzeigst, im Datums- oder Zeitformat formatiert sind, um die Lesbarkeit zu verbessern.
FAQ: Häufige Fragen
1. Wie erkenne ich, ob ein Zeitstempel in Millisekunden oder Sekunden ist?
Normalerweise sind Zeitstempel in Millisekunden länger als 10 Ziffern. Wenn du einen Zeitstempel hast, der 13 Ziffern hat, handelt es sich wahrscheinlich um Millisekunden.
2. Kann ich diese Methode auch in Excel für Mac verwenden?
Ja, die VBA-Funktion ist plattformunabhängig und sollte sowohl auf Windows- als auch auf Mac-Versionen von Excel funktionieren.
3. Wie kann ich einen UNIX-Zeitstempel in UTC umrechnen?
Nutze die GMToffset-Option in der Funktion, um die Zeit in UTC zu konvertieren, indem du den entsprechenden Offset hinzufügst oder abziehst.