반응형
Excel에서 ISO8601 날짜/시간(타임존 포함) 해석
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
매크로 대신 수식을 사용하여 표준 시간대를 사용하지 않고 ISO 타임스탬프를 해석하는 간단한 방법이 있습니다.이것은 원래 포스터의 질문과는 다릅니다만, Excel의 ISO 타임스탬프를 해석하려고 할 때 이 질문을 발견하고, 이 솔루션이 도움이 된다고 생각했기 때문에, 여기서 공유하려고 합니다.
다음 공식은 ISO 타임스탬프를 해석합니다.또, 타임 존을 포함하지 않습니다.
=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8))
그러면 부동소수점 형식으로 날짜가 생성되며, 일반 Excel 형식을 사용하여 날짜 형식을 지정할 수 있습니다.
많은 구글링들이 아무 것도 검색하지 않아서 나는 나만의 루틴을 쓴다.나중에 참조할 수 있도록 여기에 게시:
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
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
(시간대를 고려한 현지시간)
물론 밀리초 또는 + 이후 시간이 더 길면 다른 트리밍 섹션의 길이를 수정할 수 있습니다.
sigpwned표준 시간대를 무시하려면 수식을 사용합니다.
댓글을 달았으면 좋았을 텐데, 너무 아쉬워요.죄송합니다.이것은 저에게 매우 도움이 되었습니다.-감사합니다.rix0rr. 하지만 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
어플리케이션의 VB를 사용하면 다음과 같이 할 수 있습니다.
예를 들어 다음을 구문 분석합니다.
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))
위해서
2011-01-01
다음 작업을 수행합니다.
=DATEVALUE(LEFT(A1,10))
그러나 상위 날짜 형식은 Excel이 자동으로 해석해야 합니다.
그런 다음 Excel 날짜/시간 값을 얻습니다. 이 값을 날짜 및 시간으로 포맷할 수 있습니다.
자세한 내용과 샘플 파일은 http://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html 에서 확인할 수 있습니다.
제 날짜는 20130221T133551Z (YYYMMDD'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
(타임존 변환은 테스트되지 않으며 예기치 않은 입력이 발생했을 경우의 에러 처리는 없습니다)
rix0rr에 의한 답변은 훌륭하지만 콜론을 사용하지 않거나 시간만 사용하는 시간대 오프셋은 지원하지 않습니다.이 기능을 약간 강화하여 다음 포맷에 대한 지원을 추가했습니다.
'---------------------------------------------------------------------
' 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
특정(고정) 형식만 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
... 또는 "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
공식
=DATEVALUE(LEFT([@ISO], 10)) + TIMEVALUE(MID([@ISO], 12, 8)) - VALUE(RIGHT([@ISO], 5)/100)/24
[@ISO]는 ISO8601 형식의 현지시간 날짜/시간을 포함하는 셀(테이블 내)입니다.
둘 다 새로운 날짜/시간 유형 값을 생성합니다.필요에 따라 자유롭게 기능을 조정하십시오(특정 날짜/시간 형식).
위의 커스텀 VBA 기능은 검증하지 않았지만, 기능이 제한되거나 허용되지 않을 수 있습니다.그리고 이와 관련하여 위의 'Excel Formula Only' 솔루션 중 어느 것도 완전하지 않거나 정확하지 않은 것 같습니다.
따라서 날짜가 셀 내에 있는 경우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)
자세한 내용은 다음을 참조하십시오.
임의의 타임존을 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
언급URL : https://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel
반응형
'programing' 카테고리의 다른 글
| Objective-C에서 오브젝트를 캐스팅하는 방법 (0) | 2023.04.11 |
|---|---|
| Windows용 Git: .bashrc 또는 Git Bash 쉘용 동등한 구성 파일 (0) | 2023.04.11 |
| npm 5에 의해 작성된 package-lock.json 파일을 커밋해야 합니까? (0) | 2023.04.11 |
| 그림 제목과 축 레이블 글꼴 크기를 설정하려면 어떻게 해야 합니까? (0) | 2023.04.11 |
| 특정 파일만 해동하려면 어떻게 해야 합니까? (0) | 2023.04.11 |