在 Excel 中解析 ISO8601日期/时间(包括 TimeZone)

我需要在 Excel/VBA 中解析一个包含时区(来自外部源)的 ISO8601日期/时间格式到一个正常的 Excel 日期。据我所知，Excel XP (我们正在使用它)没有内置的例程，所以我想我正在寻找一个用于解析的自定义 VBA 函数。

ISO8601 datetimes look like one of these:

2011-01-01
2011-01-01T12:00:00Z
2011-01-01T12:00:00+05:00
2011-01-01T12:00:00-05:00
2011-01-01T12:00:00.05381+05:00
126360 次浏览
小开
最佳答案

A lot of Googling didn't turn up anything so I write my own routine. Posting it here for future reference:

Option Explicit


'---------------------------------------------------------------------
' Declarations must be at the top -- see below
'---------------------------------------------------------------------
Public Declare Function SystemTimeToFileTime Lib _
"kernel32" (lpSystemTime As SYSTEMTIME, _
lpFileTime As FILETIME) As Long


Public Declare Function FileTimeToLocalFileTime Lib _
"kernel32" (lpLocalFileTime As FILETIME, _
lpFileTime As FILETIME) As Long


Public Declare Function FileTimeToSystemTime Lib _
"kernel32" (lpFileTime As FILETIME, lpSystemTime _
As SYSTEMTIME) As Long


Public Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type


Public Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type


'---------------------------------------------------------------------
' Convert ISO8601 dateTimes to Excel Dates
'---------------------------------------------------------------------
Public Function ISODATE(iso As String)
' Find location of delimiters in input string
Dim tPos As Integer: tPos = InStr(iso, "T")
If tPos = 0 Then tPos = Len(iso) + 1
Dim zPos As Integer: zPos = InStr(iso, "Z")
If zPos = 0 Then zPos = InStr(iso, "+")
If zPos = 0 Then zPos = InStr(tPos, iso, "-")
If zPos = 0 Then zPos = Len(iso) + 1
If zPos = tPos Then zPos = tPos + 1


' Get the relevant parts out
Dim datePart As String: datePart = Mid(iso, 1, tPos - 1)
Dim timePart As String: timePart = Mid(iso, tPos + 1, zPos - tPos - 1)
Dim dotPos As Integer: dotPos = InStr(timePart, ".")
If dotPos = 0 Then dotPos = Len(timePart) + 1
timePart = Left(timePart, dotPos - 1)


' Have them parsed separately by Excel
Dim d As Date: d = DateValue(datePart)
Dim t As Date: If timePart <> "" Then t = TimeValue(timePart)
Dim dt As Date: dt = d + t


' Add the timezone
Dim tz As String: tz = Mid(iso, zPos)
If tz <> "" And Left(tz, 1) <> "Z" Then
Dim colonPos As Integer: colonPos = InStr(tz, ":")
If colonPos = 0 Then colonPos = Len(tz) + 1


Dim minutes As Integer: minutes = CInt(Mid(tz, 2, colonPos - 2)) * 60 + CInt(Mid(tz, colonPos + 1))
If Left(tz, 1) = "+" Then minutes = -minutes
dt = DateAdd("n", minutes, dt)
End If


' Return value is the ISO8601 date in the local time zone
dt = UTCToLocalTime(dt)
ISODATE = dt
End Function


'---------------------------------------------------------------------
' Got this function to convert local date to UTC date from
' http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html
'---------------------------------------------------------------------
Public Function UTCToLocalTime(dteTime As Date) As Date
Dim infile As FILETIME
Dim outfile As FILETIME
Dim insys As SYSTEMTIME
Dim outsys As SYSTEMTIME


insys.wYear = CInt(Year(dteTime))
insys.wMonth = CInt(Month(dteTime))
insys.wDay = CInt(Day(dteTime))
insys.wHour = CInt(Hour(dteTime))
insys.wMinute = CInt(Minute(dteTime))
insys.wSecond = CInt(Second(dteTime))


Call SystemTimeToFileTime(insys, infile)
Call FileTimeToLocalFileTime(infile, outfile)
Call FileTimeToSystemTime(outfile, outsys)


UTCToLocalTime = CDate(outsys.wMonth & "/" & _
outsys.wDay & "/" & _
outsys.wYear & " " & _
outsys.wHour & ":" & _
outsys.wMinute & ":" & _
outsys.wSecond)
End Function


'---------------------------------------------------------------------
' Tests for the ISO Date functions
'---------------------------------------------------------------------
Public Sub ISODateTest()
' [[ Verify that all dateTime formats parse sucesfully ]]
Dim d1 As Date: d1 = ISODATE("2011-01-01")
Dim d2 As Date: d2 = ISODATE("2011-01-01T00:00:00")
Dim d3 As Date: d3 = ISODATE("2011-01-01T00:00:00Z")
Dim d4 As Date: d4 = ISODATE("2011-01-01T12:00:00Z")
Dim d5 As Date: d5 = ISODATE("2011-01-01T12:00:00+05:00")
Dim d6 As Date: d6 = ISODATE("2011-01-01T12:00:00-05:00")
Dim d7 As Date: d7 = ISODATE("2011-01-01T12:00:00.05381+05:00")
AssertEqual "Date and midnight", d1, d2
AssertEqual "With and without Z", d2, d3
AssertEqual "With timezone", -5, DateDiff("h", d4, d5)
AssertEqual "Timezone Difference", 10, DateDiff("h", d5, d6)
AssertEqual "Ignore subsecond", d5, d7


' [[ Independence of local DST ]]
' Verify that a date in winter and a date in summer parse to the same Hour value
Dim w As Date: w = ISODATE("2010-02-23T21:04:48+01:00")
Dim s As Date: s = ISODATE("2010-07-23T21:04:48+01:00")
AssertEqual "Winter/Summer hours", Hour(w), Hour(s)


MsgBox "All tests passed succesfully!"
End Sub


Sub AssertEqual(name, x, y)
If x <> y Then Err.Raise 1234, Description:="Failed: " & name & ": '" & x & "' <> '" & y & "'"
End Sub
小开

我本来可以把这个作为评论发布的，但是我没有足够的名声——对不起！.这对我来说非常有用——谢谢 rix0rrr，但是我注意到 UTCToLocalTime 函数在最后构造日期时需要考虑区域设置。下面是我在英国使用的版本——注意 wDay 和 wMonth 的顺序颠倒了:

Public Function UTCToLocalTime(dteTime As Date) As Date
Dim infile As FILETIME
Dim outfile As FILETIME
Dim insys As SYSTEMTIME
Dim outsys As SYSTEMTIME


insys.wYear = CInt(Year(dteTime))
insys.wMonth = CInt(Month(dteTime))
insys.wDay = CInt(Day(dteTime))
insys.wHour = CInt(Hour(dteTime))
insys.wMinute = CInt(Minute(dteTime))
insys.wSecond = CInt(Second(dteTime))


Call SystemTimeToFileTime(insys, infile)
Call FileTimeToLocalFileTime(infile, outfile)
Call FileTimeToSystemTime(outfile, outsys)


UTCToLocalTime = CDate(outsys.wDay & "/" & _
outsys.wMonth & "/" & _
outsys.wYear & " " & _
outsys.wHour & ":" & _
outsys.wMinute & ":" & _
outsys.wSecond)
End Function
小开

我的日期是20130221T133551Z (YYYYMMDD‘ T’HHMMSS‘ Z’) ，所以我创建了这个变体:

Public Function ISODATEZ(iso As String) As Date
Dim yearPart As Integer: yearPart = CInt(Mid(iso, 1, 4))
Dim monPart As Integer: monPart = CInt(Mid(iso, 5, 2))
Dim dayPart As Integer: dayPart = CInt(Mid(iso, 7, 2))
Dim hourPart As Integer: hourPart = CInt(Mid(iso, 10, 2))
Dim minPart As Integer: minPart = CInt(Mid(iso, 12, 2))
Dim secPart As Integer: secPart = CInt(Mid(iso, 14, 2))
Dim tz As String: tz = Mid(iso, 16)


Dim dt As Date: dt = DateSerial(yearPart, monPart, dayPart) + TimeSerial(hourPart, minPart, secPart)


' Add the timezone
If tz <> "" And Left(tz, 1) <> "Z" Then
Dim colonPos As Integer: colonPos = InStr(tz, ":")
If colonPos = 0 Then colonPos = Len(tz) + 1


Dim minutes As Integer: minutes = CInt(Mid(tz, 2, colonPos - 2)) * 60 + CInt(Mid(tz, colonPos + 1))
If Left(tz, 1) = "+" Then minutes = -minutes
dt = DateAdd("n", minutes, dt)
End If


' Return value is the ISO8601 date in the local time zone
' dt = UTCToLocalTime(dt)
ISODATEZ = dt
End Function

(未测试时区转换，并且在出现意外输入的情况下没有错误处理)

小开

有一种(相当)简单的方法可以使用公式而不是宏来解析没有时区的 ISO 时间戳。这不是原来的海报问的 没错，但我发现这个问题时，试图解析在 Excel 的 ISO 时间戳和发现 这个解决方案有用，所以我想我会在这里分享它。

下面的公式将解析 ISO 时间戳，同样没有时区:

=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))

这将生成浮点格式的日期，然后您可以使用普通的 Excel 格式将其格式化为日期。

小开

好吧的 回答是伟大的，但它不支持没有冒号的时区偏移或只有小时。我略微增强了该功能，以添加对这些格式的支持:

'---------------------------------------------------------------------
' Declarations must be at the top -- see below
'---------------------------------------------------------------------
Public Declare Function SystemTimeToFileTime Lib _
"kernel32" (lpSystemTime As SYSTEMTIME, _
lpFileTime As FILETIME) As Long


Public Declare Function FileTimeToLocalFileTime Lib _
"kernel32" (lpLocalFileTime As FILETIME, _
lpFileTime As FILETIME) As Long


Public Declare Function FileTimeToSystemTime Lib _
"kernel32" (lpFileTime As FILETIME, lpSystemTime _
As SYSTEMTIME) As Long


Public Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type


Public Type SYSTEMTIME
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type


'---------------------------------------------------------------------
' Convert ISO8601 dateTimes to Excel Dates
'---------------------------------------------------------------------
Public Function ISODATE(iso As String)
' Find location of delimiters in input string
Dim tPos As Integer: tPos = InStr(iso, "T")
If tPos = 0 Then tPos = Len(iso) + 1
Dim zPos As Integer: zPos = InStr(iso, "Z")
If zPos = 0 Then zPos = InStr(iso, "+")
If zPos = 0 Then zPos = InStr(tPos, iso, "-")
If zPos = 0 Then zPos = Len(iso) + 1
If zPos = tPos Then zPos = tPos + 1


' Get the relevant parts out
Dim datePart As String: datePart = Mid(iso, 1, tPos - 1)
Dim timePart As String: timePart = Mid(iso, tPos + 1, zPos - tPos - 1)
Dim dotPos As Integer: dotPos = InStr(timePart, ".")
If dotPos = 0 Then dotPos = Len(timePart) + 1
timePart = Left(timePart, dotPos - 1)


' Have them parsed separately by Excel
Dim d As Date: d = DateValue(datePart)
Dim t As Date: If timePart <> "" Then t = TimeValue(timePart)
Dim dt As Date: dt = d + t


' Add the timezone
Dim tz As String: tz = Mid(iso, zPos)
If tz <> "" And Left(tz, 1) <> "Z" Then
Dim colonPos As Integer: colonPos = InStr(tz, ":")
Dim minutes As Integer
If colonPos = 0 Then
If (Len(tz) = 3) Then
minutes = CInt(Mid(tz, 2)) * 60
Else
minutes = CInt(Mid(tz, 2, 5)) * 60 + CInt(Mid(tz, 4))
End If
Else
minutes = CInt(Mid(tz, 2, colonPos - 2)) * 60 + CInt(Mid(tz, colonPos + 1))
End If


If Left(tz, 1) = "+" Then minutes = -minutes
dt = DateAdd("n", minutes, dt)
End If


' Return value is the ISO8601 date in the local time zone
dt = UTCToLocalTime(dt)
ISODATE = dt
End Function


'---------------------------------------------------------------------
' Got this function to convert local date to UTC date from
' http://excel.tips.net/Pages/T002185_Automatically_Converting_to_GMT.html
'---------------------------------------------------------------------
Public Function UTCToLocalTime(dteTime As Date) As Date
Dim infile As FILETIME
Dim outfile As FILETIME
Dim insys As SYSTEMTIME
Dim outsys As SYSTEMTIME


insys.wYear = CInt(Year(dteTime))
insys.wMonth = CInt(Month(dteTime))
insys.wDay = CInt(Day(dteTime))
insys.wHour = CInt(Hour(dteTime))
insys.wMinute = CInt(Minute(dteTime))
insys.wSecond = CInt(Second(dteTime))


Call SystemTimeToFileTime(insys, infile)
Call FileTimeToLocalFileTime(infile, outfile)
Call FileTimeToSystemTime(outfile, outsys)


UTCToLocalTime = CDate(outsys.wMonth & "/" & _
outsys.wDay & "/" & _
outsys.wYear & " " & _
outsys.wHour & ":" & _
outsys.wMinute & ":" & _
outsys.wSecond)
End Function


'---------------------------------------------------------------------
' Tests for the ISO Date functions
'---------------------------------------------------------------------
Public Sub ISODateTest()
' [[ Verify that all dateTime formats parse sucesfully ]]
Dim d1 As Date: d1 = ISODATE("2011-01-01")
Dim d2 As Date: d2 = ISODATE("2011-01-01T00:00:00")
Dim d3 As Date: d3 = ISODATE("2011-01-01T00:00:00Z")
Dim d4 As Date: d4 = ISODATE("2011-01-01T12:00:00Z")
Dim d5 As Date: d5 = ISODATE("2011-01-01T12:00:00+05:00")
Dim d6 As Date: d6 = ISODATE("2011-01-01T12:00:00-05:00")
Dim d7 As Date: d7 = ISODATE("2011-01-01T12:00:00.05381+05:00")
Dim d8 As Date: d8 = ISODATE("2011-01-01T12:00:00-0500")
Dim d9 As Date: d9 = ISODATE("2011-01-01T12:00:00-05")
AssertEqual "Date and midnight", d1, d2
AssertEqual "With and without Z", d2, d3
AssertEqual "With timezone", -5, DateDiff("h", d4, d5)
AssertEqual "Timezone Difference", 10, DateDiff("h", d5, d6)
AssertEqual "Ignore subsecond", d5, d7
AssertEqual "No colon in timezone offset", d5, d8
AssertEqual "No minutes in timezone offset", d5, d9


' [[ Independence of local DST ]]
' Verify that a date in winter and a date in summer parse to the same Hour value
Dim w As Date: w = ISODATE("2010-02-23T21:04:48+01:00")
Dim s As Date: s = ISODATE("2010-07-23T21:04:48+01:00")
AssertEqual "Winter/Summer hours", Hour(w), Hour(s)


MsgBox "All tests passed succesfully!"
End Sub


Sub AssertEqual(name, x, y)
If x <> y Then Err.Raise 1234, Description:="Failed: " & name & ": '" & x & "' <> '" & y & "'"
End Sub
小开

我知道它没有 VB 模块那么优雅 但是，如果有人正在寻找一个快速公式，考虑后’+’时区以及那么这可能是它。

= DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,5))+TIME(MID(D3,18,2),0,0)

将会改变

2017-12-01T11:03+1100

到

2/12/2017 07:03:00 AM

(local time considering timezone)

显然，你可以修改不同的修剪部分的长度，如果你得到毫秒以及如果你得到更长的时间后 + 。

如果要忽略时区，请使用 sigpwned公式。

小开

你可以这样做:

例如:

2011-01-01T12:00:00+05:00
2011-01-01T12:00:00-05:00

做:

=IF(MID(A1,20,1)="+",TIMEVALUE(MID(A1,21,5))+DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)),-TIMEVALUE(MID(A1,21,5))+DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)))

为了

2011-01-01T12:00:00Z

做:

=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))

For

2011-01-01

做:

=DATEVALUE(LEFT(A1,10))

但是最高日期格式应该是 Excel 自动解析。

Then you get a Excel date/time value, which you can format to date and time.

详细信息和示例文件: http://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html

小开

如果仅将某些(固定的)格式转换为 UTC 就足够了，那么可以编写一个简单的 VBA 函数或公式。

The function/formula below will work for these formats (milliseconds will be omitted anyway):

2011-01-01T12:00:00.053+0500
2011-01-01T12:00:00.05381+0500

VBA 函数

更长，为了更好的可读性:

Public Function CDateUTC(dISO As String) As Date


Dim d, t, tz As String
Dim tzInt As Integer
Dim dLocal As Date


d = Left(dISO, 10)
t = Mid(dISO, 12, 8)
tz = Right(dISO, 5)
tzInt = - CInt(tz) \ 100
dLocal = CDate(d & " " & t)


CDateUTC = DateAdd("h", tzInt, dLocal)


End Function

... 或者“一线笔”:

Public Function CDateUTC(dISO As String) As Date
CDateUTC = DateAdd("h", -CInt(Right(dISO, 5)) \ 100, CDate(Left(dISO, 10) & " " & Mid(dISO, 12, 8)))
End Function

配方奶粉

=DATEVALUE(LEFT([@ISO], 10)) + TIMEVALUE(MID([@ISO], 12, 8)) - VALUE(RIGHT([@ISO], 5)/100)/24

[@ISO]是包含 ISO8601格式的当地时间的日期/时间的单元格(在表中)。

它们都会生成新的日期/时间类型值。

小开

我没有验证过上面的自定义 VBA 函数，但是有时候函数是受限制的，而且不允许... ... 就此而言，上面的“ Excel 唯一公式”解决方案似乎都不完整/正确。

因此，如果日期在单元格 A1中，则公式为:

=DATEVALUE(MID(A1,1,10))+IF(LEN(A1)>12,TIMEVALUE(SUBSTITUTE(LOWER(MID(A1,12,8)),"z","")),0)+IF("."=MID(A1,20,1),TIMEVALUE(CONCAT("0:0:0",MID(A1,20,6))),0)+IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=3,IF("+"=MID(A1,MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)=":"))-3,1),1,-1)*TIMEVALUE(RIGHT(A1,5)),0)

更详细的细节:

Work Supporting Formula Construction

小开

将任何时区转换为 UTC 的完整公式，输入不包括秒:

=DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,5))+(IF(MID(D3,17,1)="+",-1,1)*IFERROR(TIMEVALUE(MID(D3,18,5)),0))

Supports: 2022-03-30T08:19-01:00 2022-03-30T12:49+03:30 2022-03-30T12:19+03:00 2022-03-30T09:19Z 2022-03-30T09:19

当输入包含秒数时:

=DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,8))+(IF(MID(D3,20,1)="+",-1,1)*IFERROR(TIMEVALUE(MID(D3,21,5)),0))

支持: 2022-03-30T08:19:14-01:00 2022-03-30T12:49:14+03:30 2022-03-30T12:19:14+03:00 2022-03-30T09:19:14Z 2022-03-30T09:19:14


提交答案