Parsing an ISO8601 date/time (including TimeZone) in Excel

Question

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

Dacey

It's 2020 now, and the latest version of Excel through Office 365 still doesn't have a simple TryParseExactDate( "yyyy-MM-dd'T'HH:mm:ss", A1 ) function in its otherwise expansive formula library. What is Microsoft's excuse? :( – Commissar

Hey, it's only been around since 1988. I'm sure they'll get to it eventually. – Couthie

That's the price of a market monopoly... – Starchy

Answer 1

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

Dacey

Had to add PtrSafe before each Declare on my system. – Ingratiate

Yeah, this doesn't work. If you add a test 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. – Proffer

See #42535008 for how to make the new function permanently available in Excel. – Tiberias

Answer 2

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.

Youngstown

But this solution doesn't consider timezone convertion. – Cockcrow

This is a reasonable alternative if the timezone is irrelevant or all the same, e.g., local time zone. – Sherborne

for anyone else that searched for converting floating point to date and found nothing - once it is in this format 42482.00 you can simply format it as a date or use =year(...) or =DATE(YEAR(G1), MONTH(G1), DAY(G1)) – Shooin

You can change that 8 to a 12 to include milliseconds, if you need that and your input includes it. – Furtherance

I used this to convert the time code. Just put the HH:MM difference in the last portion and add or subtract depending on the time zone. In my case I am 6hrs behind so I subtract it. =DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-TIMEVALUE("6:00") – Yevetteyew

Another option for timezone conversion is to simply add (or subtract) the offset in hours as a fraction of 24 (e.g. =DATEVALUE(MID(C2,1,10))+TIMEVALUE(MID(C2,12,8))-(5/24) to convert from UTC to EST). – Vedic

If you use Excel in French, the same formula will be : =DATEVAL(STXT(A1;1;10))+TEMPSVAL(STXT(A1;12;8)) – Conversazione

The question explicitly asks "(including TimeZone)", so this answer does not match with the question. – Enterovirus

this formula can be used for also parsing milliseconds from a timestamp like this 2020-09-15T01:05:46.285Z : =DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,12)) – Loomis

According to the ISO 8601 wiki, 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 this solution works, 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. – Hoye

Answer 3

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.

Sadiesadira

Seconds conversion doesn't look right to me. TIME() takes parameters hours, minutes, seconds, and there are no seconds in 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)) – Fable

The last part assumes that the time zone difference is negative (not +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. – Oracular

This answer is incorrect. According to the ISO 8601 wiki, 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. – Hoye

Answer 4

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

Hindoo

I want to point out that the author asked about ISO8601 formatted datetime strings, which are consistent in field ordering. Certainly it's great that yours works for your data, but if anyone else reads this and is confused you should check out en.wikipedia.org/wiki/ISO_8601 and also xkcd.com/1179 . – Demurrage

Whoa! Blast from the past. Anyway, I didn't change anything about the field order of the ISO date. It's the local version which needs to follow local conventions. Ideally the code should figure this out, but I did say this was for use in the UK... – Hindoo

Answer 5

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

Interlocutress

unfortunately the link for the solution with the Z at the end does not exist any more. @hani - would you care to insert the solution directly, so that this answer keeps its value? – Elbaelbart

Answer 6

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)

Lathery

Answer 7

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

Enterovirus

Answer 8

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

Liesa

Answer 9

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

Oracular

Answer 10

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:

Phyllous