Hier eine kleine UDF zum umrechnen
13.11.2018 13:20:48
EtoPHG
Hallo Werner,
Diese UDF (in ein Standardmodul) rechnet die beiden Varianten von Unix-Timestamps (10 oder 13stellig) in ein XL-Datum Zeit um.
'Function: UnixTime2XL
'Converts a UNIX Timestamp to a 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
z.B. als Formel:
=UnixTime2XL(1540234226865) ergibt 22.10.2018 18:50:26.865 (als String)
=UnixTime2XL(1540234226865;;;WAHR)
oder
=UnixTime2XL(1540234226)
ergibt 22.10.2018 18:50:26 (als XL Datum/Zeit, Format der Zelle einstellen!)
Gruess Hansueli