在Excel中解析ISO8601日期/时间（包括时区）

Question

在Excel中解析ISO8601日期/时间（包括时区）

136

我需要在Excel/VBA中解析包含时区的ISO8601日期/时间格式（来自外部来源），并转换为普通的Excel日期。据我所知，Excel XP（我们正在使用的版本）没有内置用于处理此类日期格式的例程，因此我想我需要编写一个自定义的VBA函数来解析。

ISO8601日期时间格式看起来像这样:

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

- rix0rrr

42

现在是2023年3月10日。通过Office 365，目前版本的Excel仍然没有一个简单的 TryParseExactDate( "yyyy-MM-dd'T'HH:mm:ss", A1 ) 函数。这个公式库十分庞大，但为什么Microsoft还没有推出这个函数呢？ :( - Dai

6

嘿，它只存在于1988年以来。我相信他们最终会解决这个问题的。 - CoAstroGeek

这就是垄断市场的代价... - undefined

10个回答

47

很多搜索都没有找到相关内容，所以我编写了自己的程序。在此发布供日后参考：

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

在我的系统上，必须在每个“Declare”之前添加“PtrSafe”。 - Raman

4

是的，这并不起作用。如果添加一个测试 Dim d8 As Date: d8 = ISODATE("2020-01-02T16:46:00")，它是1月2日的有效ISO日期，但返回的却是2月1日...你的测试太过乐观了。 - Liam

请参考https://stackoverflow.com/questions/42534508/how-to-permanently-store-custom-function-in-excel，了解如何在Excel中永久地使用新函数。 - undefined

7

我知道它不像VB模块那样优雅但如果有人正在寻找一种快速的公式，可以考虑在'+'后考虑时区，那么这可能就是它了。

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

将会改变

2017-12-01T11:03+1100

to

2/12/2017 07:03:00 AM

（考虑时区的本地时间）

显然，如果您也有毫秒或+后面有更长的时间，则可以修改不同修剪部分的长度。

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

- Abs

1

秒转换对我来说看起来不对。TIME()需要参数小时，分钟，秒，而2017-12-01T11:03+1100中没有秒。如果有的话，例如2017-12-01T11:03:11+1100，那么我认为调用应该是TIME(0,0,MID(D3,18,2))。但更简单的方法是，我正在使用这个：=DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,8)) - tbc0

最后一部分假设时区差为负数（不是示例中的 +1100 而是 -11:00）。答案必须检查时区是否为 +、- 或 Z。如果您打算将其转换为本地时间，则需要添加类似于 +TIME($A$1) 的内容，其中 A1 包含本地时间的时间差。 - oriadam

这个答案是不正确的。根据 ISO 8601 维基百科的说明，"18:30Z"、"22:30+04"、"1130−0700" 和 "15:00−03:30" 这些时间都指代同一个时刻，所以如果目的只是为了显示本地日期时间，那么时区偏移量就不应该被应用（因为日期时间已经是本地的了）。但是，如果目标是在 UTC 中显示日期时间，并且时区偏移量为 "+"，则必须减去时间偏移量而不是添加它（反之亦然），否则你会将偏移量应用到已经应用过偏移量的日期时间上。 - curious_prism

5

您可以不使用VBA来完成此操作：例如，解析以下内容：

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

do:

=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)))

For

2011-01-01T12:00:00Z

做：

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

为了

2011-01-01

做：

=DATEVALUE(LEFT(A1,10))

但是，上面的日期格式应该由Excel自动解析。

然后您会得到一个Excel日期/时间值，您可以将其格式化为日期和时间。

有关详细信息和示例文件，请参阅：http://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html

- Hani

不幸的是，带有 Z 结尾的解决方案链接已经不存在了。@hani - 您是否愿意直接插入解决方案，以便这个答案保持其价值？ - luksch

5

我本来想把这篇文章作为评论发表，但我没有足够的声望 - 抱歉！对我来说这真的很有用 - 谢谢 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

- dsl101

1

我想指出作者询问的是ISO8601格式的日期时间字符串，这些字符串在字段排序上是一致的。当然，如果其他人阅读此内容并感到困惑，您应该查看https://en.wikipedia.org/wiki/ISO_8601和https://xkcd.com/1179/。虽然你的代码适用于你的数据很好，但需要注意这一点。 - Hovis Biddle

3

哇！来自过去的轰击。无论如何，我没有改变ISO日期字段顺序的任何内容。它是“本地”版本需要遵循本地惯例。理想情况下，代码应该能够解决这个问题，但我确实说过这是在英国使用的…… - dsl101

2

我的日期格式为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

（时区转换未经测试，如果输入不符合预期，将没有错误处理）

- Rolf Rander

2

rix0rrr的答案很好，但不支持没有冒号或仅带小时的时区偏移量。我稍微改进了这个函数以支持这些格式:

'---------------------------------------------------------------------
' 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

- Bert

1

将任何时区转换为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))

支持：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

- oriadam

0

我尚未验证上述自定义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)

... 有关更详细的分解，请参见：

- George 2.0 Hope

0

如果您只需要将某些（固定）格式转换为UTC，则可以编写一个简单的VBA函数或公式。

以下函数/公式适用于以下格式（毫秒将被省略）：

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格式显示。

两者都会生成新的日期/时间类型值。根据您的需要自由调整函数（特定的日期/时间格式）。

- CraZ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- sigpwned · Accepted Answer

有一种（相对）简单的方法可以使用公式而非宏来解析ISO时间戳WITHOUT时区。这不是原帖所要求的完全方式，但当我在尝试解析Excel中的ISO时间戳时，发现这个解决方案很有用，所以我想在这里分享。

以下公式将解析ISO时间戳，同样WITHOUT时区：

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

这将产生浮点格式的日期，然后您可以使用常规的Excel格式将其格式化为日期。