vba-tools / vba-utc Goto Github PK

I am having issue with VBA.Format$ - this could be related to my regional settings (finnish)

For some reason function ConvertToIso(utc_LocalDate As Date) As String with parameter "27.4.2020 17.00.00":
VBA.Format$(ConvertToUtc(utc_LocalDate), "yyyy-mm-ddTHH:mm:ss.000Z")
->converts to "2020-04-27T14.00.00.000Z" (not correct)
If I modify it to
VBA.Format$(ConvertToUtc(utc_LocalDate), "yyyy-mm-ddTHH:mm:ss.000Z")
->converts to "2020-04-27T14:00.00.000Z" (not correct but one . is : as it shuold be)
So correct one would be:
VBA.Format$(ConvertToUtc(utc_LocalDate), "yyyy-mm-ddTHH:mm:ss.000Z")

See at bottom for ():
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications

Need to update handling of Long and LongPtr.

Related: VBA-tools/VBA-Web#248 and VBA-tools/VBA-JSON#86

When parsing on a system where the decimal separator is not a period, the ParseIso method fails.

need to modify (for example) :
VBA.CInt(utc_TimeParts(2))
to:
VBA.CInt(Strings.Replace(utc_TimeParts(2), ".", Strings.Mid(Strings.Format(1.1, "#.#"), 2, 1)))

Problem still seems not solved.
I corrected it by using:
ParseIso = ParseIso - utc_Offset
instead of
ParseIso = ParseIso + utc_Offset

2017-12-29T23:12:48.813+01:00 should be 2017-12-29 23:12:48 in my timezone +01:00 Vienna.
The Offset is not added - it must be inverted (as i saw on wiki).

Sorry: had to edit this a lot as I had the wrong cause orginally.

The ParseIso method gets the wrong date if there is no : between the hours and minutes in the timezone

e.g. ParseIso("2024-03-11T17:40:00.000+0800") gives 07/02/2024 09:00:40

ParseIso("2024-03-11T17:40:00.000+08:00") gives the correct value 11/03/2024 09:40:00

From VBA-tools/VBA-Web#233

The ParseIso() function in the WebHelpers module doesn't seem to handle offsets correctly.

JSON ISO String with 0 offset: "2016-06-01T01:08:20.9333502+00:00" Is not treated as being UTC and doesn't get the local timezone added to it... It simply gets shown as is.

JSON ISO String with 12 hour offset:"2016-06-01T13:08:20.9333502+12:00" Gets its 12 hour offset added to it again which sets it 12 hours into the future...

Correct me if I'm wrong here but I believe that with ISO 8601 the offset specifies how far away the stated date/time is from UTC.

So to get the second example's JSON ISO String in your local time you would first subtract 12 hours to get it to a UTC time and then add your local timezone offset to get it in local time.

Converting between VBA Dates and seconds since the unix epoch would be useful things to have. Do they belong in this library?