I need to parse an ISO8601 date/time format with an included timezone (from an external source) in Excel/VBA, to a normal Excel Date. As far as I can tell, Excel XP (which is what we're using) doesn't have a routine for that built-in, so I guess I'm looking at a custom VBA function for the parsing.
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 There is a (reasonably) simple way to parse an ISO timestamp WITHOUT the time zone using formulas instead of macros. This is not exactly what the original poster has asked, but I found this question when trying to parse ISO timestamps in Excel and found this solution useful, so I thought I would share it here.
The following formula will parse an ISO timestamp, again WITHOUT the time zone:
=DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8)) This will produce the date in floating point format, which you can then format as a date using normal Excel formats.
8 to a 12 to include milliseconds, if you need that and your input includes it.
=DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-TIMEVALUE("6:00")
=DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-(5/24) to convert from UTC to EST).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 
PtrSafe before each Declare on my system.
Dim d8 As Date: d8 = ISODATE("2020-01-02T16:46:00") that is a valid ISO date for 2nd of Jan, bit it returns 1st Feb... Your tests are very optimistic.
Public Declare PtrSafe Function ...I know its not as elegant as VB module but if someone is looking for a quick formula which consider time zone after '+' as well then this could be it.
= DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,5))+TIME(MID(D3,18,2),0,0) will change
2017-12-01T11:03+1100 to
2/12/2017 07:03:00 AM (local time considering timezone)
obviously, u can modify the length of different trimming sections, if u got milliseconds as well or if you got longer time after +.
use sigpwned formula if you want to ignore timezone.
2017-12-01T11:03+1100. If there were, e.g. 2017-12-01T11:03:11+1100 then I think the call should be TIME(0,0,MID(D3,18,2)). But more simply, I'm using this: =DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,8))
+1100 as in the example but -11:00). The answer must check if the time zone is +, - or Z. If you intended to convert it to local time you need to add something like +TIME($A$1) where A1 contains the time difference of local time.
the following times all refer to the same moment: "18:30Z", "22:30+04", "1130−0700", and "15:00−03:30" - so if the goal is simply to to display the local datetime, then the timezone offset should NOT be applied (since the datetime is already local), however if the goal is to display the datetime in UTC, when the timezone offset is "+", then you must subtract the time offset, NOT add it (visa versa), otherwise you will be applying the offset to a datetime that has already had the offset applied to it.I would have posted this as a comment, but I don't have enough rep - sorry!. This was really useful for me - thanks rix0rrr, but I noticed that the UTCToLocalTime function needs to take account of regional settings when constructing the date at the end. Here's the version I use in the UK - note that the order of wDay and wMonth are reversed:
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 
You can do this w/o VB for Applications:
E.g. to parse the following:
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 do:
=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8)) For
2011-01-01 do:
=DATEVALUE(LEFT(A1,10)) but the upper date format should Excel parse automatically.
Then you get a Excel date/time value, which you can format to date and time.
For detailed information and sample files: http://blog.hani-ibrahim.de/iso-8601-parsing-in-excel-and-calc.html
A complete formula for converting any time zone to UTC, input does not include seconds:
=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
When input include seconds:
=DATEVALUE(MID(D3,1,10))+TIMEVALUE(MID(D3,12,8))+(IF(MID(D3,20,1)="+",-1,1)*IFERROR(TIMEVALUE(MID(D3,21,5)),0))
Supports: 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
My dates are on the form 20130221T133551Z (YYYYMMDD'T'HHMMSS'Z') so I created this variant:
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 (the timezone-conversion is not tested, and there is no error-handling in case of unexpected input)
The answer by rix0rrr is great, but it does not support time zone offsets without colon or with only hours. I slightly enhanced the function to add support for these formats:
'--------------------------------------------------------------------- ' 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 If it's sufficient for you to convert just a certain (fixed) formats to UTC, you can write a simple VBA function or formula.
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 function
Longer, for better readibility:
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 ... or a "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 Formula
=DATEVALUE(LEFT([@ISO], 10)) + TIMEVALUE(MID([@ISO], 12, 8)) - VALUE(RIGHT([@ISO], 5)/100)/24 [@ISO] is the cell (within a table) containing the date/time in local time in ISO8601 format.
Both will generate new date/time type value. Feel free to adjust functions accordingly to your needs (specific date/time format).
I've not verified the above custom VBA functions, but there are times where functions are restricted, and not permitted... and with respect to that, none of the 'Excel Formula Only' solutions above seemed to be either complete / correct.
Therefore, if the date is in cell A1, the formula would be:
=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) ... for a more detailed breakdown:
TryParseExactDate( "yyyy-MM-dd'T'HH:mm:ss", A1 )function in its otherwise expansive formula library. What is Microsoft's excuse? :(