Przetwarzanie daty / godziny ISO8601 (w tym TimeZone) w programie Excel

85

Muszę przeanalizować format daty / godziny ISO8601 z dołączoną strefą czasową (z zewnętrznego źródła) w programie Excel / VBA, do normalnej daty programu Excel. O ile wiem, Excel XP (którego używamy) nie ma wbudowanej procedury, więc myślę, że patrzę na niestandardową funkcję VBA do analizowania.

Czasy danych ISO8601 wyglądają następująco:

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
excel  datetime  vba  timezone  iso8601 
— rix0rrr
źródło
1
Jest rok 2020, a najnowsza wersja programu Excel za pośrednictwem Office 365 nadal nie ma prostej TryParseExactDate( "yyyy-MM-dd'T'HH:mm:ss", A1 )funkcji w swojej obszernej bibliotece formuł. Jaka jest wymówka Microsoftu? :(
— Dai

Odpowiedzi:

168

Istnieje (rozsądnie) prosty sposób analizowania znacznika czasu ISO BEZ strefy czasowej przy użyciu formuł zamiast makr. Nie jest to dokładnie to, o co pytał oryginalny plakat, ale znalazłem to pytanie, próbując przeanalizować znaczniki czasu ISO w programie Excel i uznałem to rozwiązanie za przydatne, więc pomyślałem, że podzielę się nim tutaj.

Poniższa formuła przeanalizuje znacznik czasu ISO, ponownie BEZ strefy czasowej:

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

Spowoduje to utworzenie daty w formacie zmiennoprzecinkowym, który można następnie sformatować jako datę przy użyciu zwykłych formatów programu Excel.

— sigpwned
źródło
4
Dziwne, że to nie została zaakceptowana odpowiedź. To znacznie prostsze niż reszta.
— Travis Griggs
6
Ale to rozwiązanie nie uwzględnia konwersji strefy czasowej.
— Goku
1
Jest to rozsądna alternatywa, jeśli strefa czasowa nie ma znaczenia lub jest taka sama, np. Lokalna strefa czasowa.
— kevinarpe
5
Możesz zmienić to 8na a, 12aby uwzględniać milisekundy, jeśli tego potrzebujesz, a dane wejściowe to uwzględniają.
— gilly3
3
Użyłem tego do konwersji kodu czasowego. Po prostu wstaw różnicę GG: MM w ostatniej porcji i dodaj lub odejmij w zależności od strefy czasowej. W moim przypadku spóźniłem się o 6 godzin, więc to odejmuję. =DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-TIMEVALUE("6:00")
— chaiboy
44

Wiele Googlów nic nie przyniosło, więc piszę własny program. Publikowanie go tutaj do wykorzystania w przyszłości:

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
źródło
Musiałem dodać PtrSafeprzed każdym Declarew moim systemie.
— Raman
1
Tak, to nie działa. Jeśli dodasz test, Dim d8 As Date: d8 = ISODATE("2020-01-02T16:46:00")który jest prawidłową datą ISO na 2 stycznia, bit zwraca 1 lutego ... Twoje testy są bardzo optymistyczne.
— Liam
5

Opublikowałbym to jako komentarz, ale nie mam wystarczającej liczby przedstawicieli - przepraszam !. To było dla mnie bardzo przydatne - dzięki rix0rrr, ale zauważyłem, że funkcja UTCToLocalTime musi uwzględniać ustawienia regionalne podczas konstruowania daty na końcu. Oto wersja, której używam w Wielkiej Brytanii - zwróć uwagę, że kolejność wDay i wMonth jest odwrócona:

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
źródło
Chcę zwrócić uwagę, że autor zapytał o ciągi daty i godziny sformatowane w ISO8601, które są spójne w porządkowaniu pól. Z pewnością świetnie, że twoje dane działają z twoimi danymi, ale jeśli ktoś to czyta i jest zdezorientowany, powinieneś sprawdzić en.wikipedia.org/wiki/ISO_8601, a także xkcd.com/1179 .
— Hovis Biddle,
2
Whoa! Powiew przeszłości. W każdym razie nic nie zmieniłem w kolejności pól daty ISO. To lokalna wersja, która musi być zgodna z lokalnymi konwencjami. Idealnie kod powinien to zrozumieć, ale powiedziałem, że jest to używane w Wielkiej Brytanii ...
— dsl101
2

Odpowiedź przez rix0rrr jest super, ale nie obsługuje przesunięcia strefy czasowej bez okrężnicy lub tylko godziny. Nieznacznie ulepszyłem funkcję, aby dodać obsługę tych formatów:

'---------------------------------------------------------------------
' 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
źródło
2

Wiem, że nie jest tak elegancki jak moduł VB, ale jeśli ktoś szuka szybkiej formuły uwzględniającej strefę czasową po „+”, to może to być to. 

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

ulegnie zmianie 

2017-12-01T11:03+1100

do 

2/12/2017 07:03:00 AM

(czas lokalny z uwzględnieniem strefy czasowej)

oczywiście, możesz modyfikować długość różnych sekcji przycinania, jeśli masz również milisekundy lub jeśli masz dłuższy czas po +.

użyj sigpwnedformuły, jeśli chcesz zignorować strefę czasową.

— Abs
źródło
2

Możesz to zrobić bez VB dla aplikacji:

Na przykład, aby przeanalizować następujące elementy:

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

zrobić:

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

Dla

2011-01-01T12:00:00Z

zrobić:

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

Dla

2011-01-01

zrobić:

=DATEVALUE(LEFT(A1,10))

ale format daty górnej powinien automatycznie analizować program Excel.

Następnie otrzymasz wartość daty / godziny programu Excel, którą możesz sformatować do daty i godziny.

Szczegółowe informacje i przykładowe pliki: http://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html

— Hani
źródło
niestety link do rozwiązania z Z na końcu już nie istnieje. @hani - czy zechciałbyś wstawić rozwiązanie bezpośrednio, aby ta odpowiedź zachowała swoją wartość?
— luksch
1

Moje daty są na formularzu 20130221T133551Z (RRRRMMDD'T'HHMMSS'Z '), więc stworzyłem ten wariant:

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

(konwersja strefy czasowej nie jest testowana i nie ma obsługi błędów w przypadku nieoczekiwanego wejścia)

— Rolf Rander
źródło
0

Jeśli wystarczy przekonwertować tylko niektóre (ustalone) formaty na UTC, możesz napisać prostą funkcję lub formułę VBA.

Poniższa funkcja / formuła będzie działać dla tych formatów (milisekundy i tak zostaną pominięte):

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

Funkcja VBA

Dłużej, dla lepszej czytelności:

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

... lub „oneliner”:

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

Formuła

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

[@ISO] to komórka (w tabeli) zawierająca datę / godzinę w czasie lokalnym w formacie ISO8601.

Oba wygenerują nową wartość typu daty / godziny. Możesz dowolnie dostosowywać funkcje do swoich potrzeb (określony format daty / czasu).

— CraZ
źródło
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.