search

VBA-tools / VBA-UTC

UTC and ISO 8601 date conversion and parsing for VBA
MIT License
56 stars 28 forks source link

If the time zone format omits a colon, ParseIso returns an incorrect result #8

Open mattybrown1985 opened 1 year ago

mattybrown1985 commented 1 year ago
Input Output Expected Output
ParseIso("2022-11-29T14:52:41.689+01:00") 29/11/2022 13:52:41 29/11/2022 13:52:41
ParseIso("2022-11-29T14:52:41.689+0100") 25/11/2022 10:52:41 29/11/2022 13:52:41
Nick-vanGemeren commented 1 year ago

Since ParseIso does pattern matching on the cheap, it has several 'features' and bugs. One feature is that it only supports ISO8601 strings in 'extended format' (with all separators).

In this case, the offset is taken as 100 hours and so the output is the local time in zone UTC+1, 4 days 3 hours behind.

It's possible to support most of the various flavours of ISO 8601 strings using regular expressions, but this would be significant rewrite of ParseIso including a new library reference.

If you just want to support 'basic' offsets: in utcConverter or JsonConverter, find the ParseIso function. Change the last line of

                Select Case UBound(utc_OffsetParts)
                Case 0
                    utc_offset = TimeSerial(VBA.CInt(utc_OffsetParts(0)), 0, 0)

to

                    Dim OffsetHours As Long, OffsetMins As Long
                    Dim OffsetStr As String: OffsetStr = utc_OffsetParts(0)
                    If Len(OffsetStr) <= 2 Then OffsetStr = OffsetStr & "00"
                    OffsetMins = VBA.CInt(VBA.Right$(OffsetStr, 2))
                    OffsetHours = (VBA.CInt(OffsetStr) - OffsetMins) / 100
                    utc_Offset = TimeSerial(OffsetHours, OffsetMins, 0)

If this solves your problem, please close this issue here.