search

VBA-tools / VBA-UTC

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

Formatting with \ #6

Open angex opened 4 years ago

angex commented 4 years ago

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

Nick-vanGemeren commented 2 years ago

This issue report looked weird at first because nothing changed in the displayed code. Given the issue title, I think several backslashes are not displayed because they're not formatted as code. The ISO standard wants specific separators which should not be regionalised. So the time and decimal separators need to be escaped with backslash:

ConvertToIso = VBA.Format$(ConvertToUtc(utc_LocalDate), "yyyy-mm-ddTHH\:mm\:ss\.000Z") (Looks OK in Preview)

The '-' T and Z could be escaped but don't mean anything to Format.