You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
ConvertToIso should have from/to timezone capability #169
Public Function ConvertToIso(utc_LocalDate As Date) As String
The behaviour currently treat the parameter as being in the "local" timezone, and without being asked, converts it to UTC and returns the ISO 8601 with "Z" timezone. This is because VBA "dates" are (in Python terminology) "timezone-naive".
Proposed new signature:
Public Function ConvertToIso(utc_LocalDate As Date, Optional fromTZhours As Variant = Empty, Optional toTZhours As Double = 0) As String
This would be backwards-compatible in preserving the old behaviour by default. It would make the passed-in date be "timezone-aware", but defaulting to local.
The local TZ would be detected with an "empty" fromTZhours (using IsEmpty()), and converting the input date into a real UTC one, effectively timezone-aware. The fromTZhours value would be either "empty" (local), or a Double of hours.
It would then be converted to one in the toTZhours, by multiplying that quantity by 60 and applying that as a DateDiff in minutes (which catches offsets with non-integer number of hours, which do exist). The timezone string would be calculated appropriately, with "Z" as a special case for 0.
An ISO date is in UTC format which is a global time unit. As described in provided documentation link above the function utc_TzSpecificLocalTimeToSystemTime. As far as I understand an ISO time unit is independent of local times. Thus adding or substracting hours in reference to your local time.
What happens in the function ConvertToJson is that when the json value is of type VBA.vbDate then the datevalue is converted to ISO by the ConvertToIso function, thus adding or substracting hours.
However, when parsing the json value, it will never call the ParseISO method (and correct the time back to your local time unit). This is the real problem (at least for me). I think the writer either forgot (because his local time zone is +0 hours to UTC time) or because testing if a string is actually an ISO date is tricky as it can have multiple formats (see here for example). Above that, another reason for not implementing this yet might be because a json-value might already be in local time format and therefor not need time adjustment.
I use this module to store values temporary. I noticed a time of 'now' (13:06 in afternoon) was stored as 2021-05-14T11:06:51.000Z, and when converted back, the same string was given. I solved it for myself by adding 1 line of code to the module, checking only for 1 format of ISO - the same one that is used to convert a date to JSON (see ConvertToISO method, "yyyy-mm-ddTHH:mm:ss.000Z").
By adding the following line of code in the json_ParseString method, conversion is happing both ways, when converting to and parsing from json.
If json_ParseString Like "####-##-##T##:##:##.###Z" Then json_ParseString = ParseIso(json_ParseString)
So the block of code becomes:
Case json_Quote
json_ParseString = json_BufferToString(json_Buffer, json_BufferPosition)
If json_ParseString Like "####-##-##T##:##:##.###Z" Then json_ParseString = ParseIso(json_ParseString)
json_Index = json_Index + 1
Exit Function
Case Else
I'm only assuming you encountered the same problem as you are posting this in the VBA-JSON repository, and not in the repository where he got this code from (his UTC-converter).
im not sure if this is the same issue, but im using this libaray, and if a date of 1/31/1999 is passed to the ConvertToIso functions, it returns: 1999-01-31T05:00:00.000Z.
what i want is to not have that timezone shifted hour, instead i would like 1999-01-31T00:00:00.000Z
Your use of 1/31/1999 (M/D/Y) suggests that you are in the US with a local time zone UTC-5. So your VBA date 36191 = local 31 Jan 1999 00:00 is converted to the international standard at UTC+0 = 1999-01-31T05:00:00.000Z. That is the value you should use for communication. An alternative is 1999-01-31T00:00:00.000-05:00; any ISO8601 parsing routine should understand both.
1999-01-31T00:00:00.000Z is a different point in time. If you really want a string in local time, you can convert directly: localISO = Format(localDate,"yyyy-mm-ddThh:mm:ss")
Your use of 1/31/1999 (M/D/Y) suggests that you are in the US with a local time zone UTC-5. So your VBA date 36191 = local 31 Jan 1999 00:00 is converted to the international standard at UTC+0 = 1999-01-31T05:00:00.000Z. That is the value you should use for communication. An alternative is 1999-01-31T00:00:00.000-05:00; any ISO8601 parsing routine should understand both.
1999-01-31T00:00:00.000Z is a different point in time. If you really want a string in local time, you can convert directly: localISO = Format(localDate,"yyyy-mm-ddThh:mm:ss")
Thanks much. yes i need it in local time w/o the timezone. i need it to match the daily eod data i pull from Bloomberg, which comes in like this '2022-02-01 00:00:00.000" from what i see.
The string that you show from Bloomberg is not an ISO8601 string (no T) and has no time zone indication (so presumed your local time). Is that what you see in the raw data or has it been massaged by a JSON converter or Bloomberg wrapper …? Is that an actual String value in VBA or a display of some other variable type?
I can’t see a problem here. Maybe post some code and data and describe what unexpected results you get?
'2022-02-01 00:00:00.000" would be a ‘end of local day’ for 31 Jan 2022, not 31 Jan 1999.
The string that you show from Bloomberg is not an ISO8601 string (no T) and has no time zone indication (so presumed your local time). Is that what you see in the raw data or has it been massaged by a JSON converter or Bloomberg wrapper …? Is that an actual String value in VBA or a display of some other variable type?
I can’t see a problem here. Maybe post some code and data and describe what unexpected results you get?
'2022-02-01 00:00:00.000" would be a ‘end of local day’ for 31 Jan 2022, not 31 Jan 1999.
The data in the json receieved from the bbg request looks like this:
So reading and loading to a pandas dataframe it ends up without a timezone. Im fine with that - as i can assume the source data is EST locale.
In excel, i allow override of this data. if the date in the excel column is "1/31/1999", after conversion using ConvertToIso, it ends up with a timezone part like this 1999-01-31T05:00:00.000Z
If i upload that to the database like this, i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps. so thats the problem im trying to solve. I would prefer solving it by massaging or coercing the data from the client excel, and leaving the backend as is to support flexibility of storing dates + timestamped data.
We'll forgive you the backtick problems. Use the Preview tab to check formatting before posting.
I am not familiar with the Bloomberg interface or 'pandas dataframe'.
There are two datetimes in that JSON. I think you are referring by 'it' to the DATE value. The DL_SNAPSHOT_START_TIME is probably not relevant. I don't immediately see where you get two datetimes going to the database.
The DATE value is just a simple date string, not date-time as you wrote.
If you are analysing the JSON with JsonConverter's ParseJson, its (VBA-UTC's) ParseISO function will return the DATE value as a local Date (sidestepping a bug for strings with time but without time zone).
So the Excel cell should have a clean Date value (without fraction). It's a bit weird to allow users to modify historical data, but ... OK as long as they only input a valid date.
The cell is then exported to the database as a text string. The database can probably accept several formats which it will convert to its internal format. A tolerant database could accept any of these as equivalents (Withthe cell Range):
"05/25/2006" = .Text ' local date format
"2006-05-25" = Format(.Value, "yyyy-mm-dd") ' ISO date (local)
"2006-05-25T00:00:00" = Format(.Value, "yyyy-mm-ddThh:mm:ss") ' ISO datetime (local)
"2006-05-25T05:00:00Z" = ConvertToIso(.Value) ' ISO datetime (global)
If the database does not handle the field as a datetime, you should get that corrected.
i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps
Please explain this and show the practical results.
We'll forgive you the backtick problems. Use the Preview tab to check formatting before posting.
I am not familiar with the Bloomberg interface or 'pandas dataframe'.
There are two datetimes in that JSON. I think you are referring by 'it' to the DATE value. The DL_SNAPSHOT_START_TIME is probably not relevant. I don't immediately see where you get two datetimes going to the database.
The DATE value is just a simple date string, not date-time as you wrote.
If you are analysing the JSON with JsonConverter's ParseJson, its (VBA-UTC's) ParseISO function will return the DATE value as a local Date (sidestepping a bug for strings with time but without time zone).
So the Excel cell should have a clean Date value (without fraction). It's a bit weird to allow users to modify historical data, but ... OK as long as they only input a valid date.
The cell is then exported to the database as a text string. The database can probably accept several formats which it will convert to its internal format. A tolerant database could accept any of these as equivalents (Withthe cell Range):
"05/25/2006" = .Text ' local date format
"2006-05-25" = Format(.Value, "yyyy-mm-dd") ' ISO date (local)
"2006-05-25T00:00:00" = Format(.Value, "yyyy-mm-ddThh:mm:ss") ' ISO datetime (local)
"2006-05-25T05:00:00Z" = ConvertToIso(.Value) ' ISO datetime (global)
If the database does not handle the field as a datetime, you should get that corrected.
i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps
Please explain this and show the practical results.
Ok let me step back and explain this more clearly as i think there is some confusion here.
There are two parts to the process. A nightly python process that uses the bloomberg api to fetch data for some securities and upsert(insert or update) those into a timeseries table in the database.
The 2nd part is an excel spreadsheet that allows a user to specify and update their own data for a security. this data is read from the excel spreadsheet, converted to json using the JsonConverter library and posted to a backend webservice to upsert (insert or update) into the database's timeseries table, with key being security and date.
Effectively, this allows a user to overwrite the data downloaded if they like.
an example of data uploaded from bloomberg would insert a date that when queried in the database, would show like this this: 2022-01-31 00:00:00.000
on the other hand, data posted from the excel spreadsheet, as mentioned is converted to json using the JsonConverter library and when received by the backend python webservice looks like this in the json: 2006-01-31T05:00:00Z and when inserted and queried in the database, looks like this: 2022-01-31 05:00:00.000
So now i have two rows for ticker XYZ with the same date, but different time:
identifier |date |px_last|
--------------+--------------------------+--------+
XYZ |2022-01-31 00:00:00.000| 30.23| <--overnight process
XYZ |2022-01-31 05:00:00.000| 32.25| <--uploaded from excel
So now when i ask for data for 2022-01-31, there are two records instead of 1. IN the above example, i can solve if i can make sure the data posted from excel does not have the timezone part added or set it such that it's '00:00:00.000'. if that is the case, the upsert function will update and not insert, since a record with the same ticker and date value exists already.
From that explanation, it's clear that the time zone info is being lost by or after the backend web service. Either the service is badly interpreting the JSON (and not even raising an error on a string it doesn't understand) or there is a problem in the update process. Fixing that will be better than trying to work around the bug(s).
You should also check the application design will still give sensible results if some users move to Chicago (CDT) or elsewhere. This could avert a crisis later.
If you really can't wait for the web service to be fixed, I've found a temporary workaround:
When you are building the Dictionary that you will convert to JSON, instead of copying the Date value, format it into the local ISO datetime string. No ConvertToIso call will be needed.
If you really can't wait for the web service to be fixed, I've found a temporary workaround: When you are building the Dictionary that you will convert to JSON, instead of copying the Date value, format it into the local ISO datetime string. No ConvertToIso call will be needed.
There isnt an issue with the backend really. its doing the right thing. i just need the front end to NOT pass the timezone part or be able to generate a json where time is in EDT/EST instead of UTC.
your solution is the that's the crux of the problem. im using the JsonCOnverter library and it doesnt give me an option not to use ConvertToIso or to specify the timezone i want to use. it just does it. so id have to change code in that libarary, whcih would effectively mean id have to maintain it going forth. not something i want to do.
for now on the back-end im just forcing a conversion to EDT. its a bit of a hack
You definitely do not need to change JsonConverter. I've given aworkaround, but I think fixing the web service is better. Upgrading to a database which understands international time would be even better.
Another approach is to follow Bloomberg and stop defining the DATE as a point in time, but more a 'market day' or 'overnight run day identifier'. You can then just transfer a 'yyyy-mm-dd' string (using the method of the workaround). The user's local date time doesn't get involved.
If you really can't wait for the web service to be fixed, I've found a temporary workaround: When you are building the Dictionary that you will convert to JSON, instead of copying the Date value, format it into the local ISO datetime string. No ConvertToIso call will be needed.
ultimately this does seem to be an odd issue with JsonConverter.ConvertToJson...when used in VBA, some dates are converted as offset -500 and other as -400, i dont now why.
This causes a different of whether data ends up as EST 12 midnight or EST 11:00PM in the database
The solution you provided means i would have to pre-process the data prior to sending over to using JsonConverter.
Or i would have to change JsonConverter itself.
More than likely the reason why some dates converted with -4:00 and some with -5:00 has to do with whether the date fell in EDT (Eastern Day savings Time) or EST (Eastern Standard Time). You might consider consulting Wikipedia on how date time zones work for a better understanding.
More than likely the reason why some dates converted with -4:00 and some with -5:00 has to do with whether the date fell in EDT (Eastern Day savings Time) or EST (Eastern Standard Time). You might consider consulting Wikipedia on how date time zones work for a better understanding.
and why would excel decide on one or the other from the same laptop in the same region/state/city/time?
the underlying is a date field w/ no time attributes
Excel/VBA does not know your time zone. The regional settings for your operating system determine the time zone. System calls are provided for conversion of local date-times to UTC or other time zones.
VBA-UTC is a little lazy and uses system calls which only apply the current algorithm for daylight saving time. New York has been using the same algorithm for many years: EST->EDT second Sunday of March, EDT->EST first Sunday of November. So your results for 2015 are correct since they bridge the change on Nov 1, 2015 02:00.
Your web service should also use system calls to convert back to local time, if your database can't accept UTC time.
See my previous posts about getting round this 'point in time' issue.
Excel/VBA does not know your time zone. The regional settings for your operating system determine the time zone. System calls are provided for conversion of local date-times to UTC or other time zones. VBA-UTC is a little lazy and uses system calls which only apply the current algorithm for daylight saving time. New York has been using the same algorithm for many years: EST->EDT second Sunday of March, EDT->EST first Sunday of November. So your results for 2015 are correct since they bridge the change on Nov 1, 2015 02:00. Your web service should also use system calls to convert back to local time, if your database can't accept UTC time.
See my previous posts about getting round this 'point in time' issue.
The database can handle utc. its timescaledb and is home to over 20mill rows of data from bloomberg, most through nightly python loader.
This data however comes from an excel spreadsheet - either for back filling purposes or custom calculations.
You said something interesting...the data im uploading for testing is month end only data..i wonder if based on the month, EST or EDT is being applied. I bet that's the issue...
New York changed algorithm in spring 2007. The old one was: EST->EDT first Sunday of April, EDT->EST last Sunday of October. It seems likely that VBA-UTC (under Windows) will convert incorrectly date-times before 2007 and in the weeks near the start of April and November. On Mac a different conversion method ias used, which may function correctly.
So after all these digressions, the original proposal of a backward-compatible enhancement to the specific function mentioned in the OP has still not been implemented. Is there a reason for that?
Activity
mohawk2 commentedon Jun 16, 2020
I don't see an alternative to making a proper timezone-aware datetime object. I suggest this, which has worked for me. If you want, I will be pleased to make a PR for it (it does utilise
TimeZoneConverterfrom https://stackoverflow.com/questions/3120915/get-timezone-information-in-vba-excel/46052360#46052360 which would need to be incorporated):mohawk2 commentedon Jun 16, 2020
And the
Iso8601Functionsmentioned, separated out as they are all pure functions:jonadv commentedon May 14, 2021
An ISO date is in UTC format which is a global time unit. As described in provided documentation link above the function utc_TzSpecificLocalTimeToSystemTime. As far as I understand an ISO time unit is independent of local times. Thus adding or substracting hours in reference to your local time.
What happens in the function ConvertToJson is that when the json value is of type VBA.vbDate then the datevalue is converted to ISO by the ConvertToIso function, thus adding or substracting hours.
However, when parsing the json value, it will never call the ParseISO method (and correct the time back to your local time unit). This is the real problem (at least for me). I think the writer either forgot (because his local time zone is +0 hours to UTC time) or because testing if a string is actually an ISO date is tricky as it can have multiple formats (see here for example). Above that, another reason for not implementing this yet might be because a json-value might already be in local time format and therefor not need time adjustment.
I use this module to store values temporary. I noticed a time of 'now' (13:06 in afternoon) was stored as 2021-05-14T11:06:51.000Z, and when converted back, the same string was given. I solved it for myself by adding 1 line of code to the module, checking only for 1 format of ISO - the same one that is used to convert a date to JSON (see ConvertToISO method, "yyyy-mm-ddTHH:mm:ss.000Z").
By adding the following line of code in the json_ParseString method, conversion is happing both ways, when converting to and parsing from json.
So the block of code becomes:
I'm only assuming you encountered the same problem as you are posting this in the VBA-JSON repository, and not in the repository where he got this code from (his UTC-converter).
Convert dates back from ISO when parsed
dss010101 commentedon Apr 9, 2024
im not sure if this is the same issue, but im using this libaray, and if a date of
1/31/1999is passed to the ConvertToIso functions, it returns:1999-01-31T05:00:00.000Z.what i want is to not have that timezone shifted hour, instead i would like
1999-01-31T00:00:00.000Zanyone know how to solve?
Nick-vanGemeren commentedon Apr 10, 2024
Your use of 1/31/1999 (M/D/Y) suggests that you are in the US with a local time zone UTC-5. So your VBA date 36191 = local 31 Jan 1999 00:00 is converted to the international standard at UTC+0 = 1999-01-31T05:00:00.000Z. That is the value you should use for communication. An alternative is 1999-01-31T00:00:00.000-05:00; any ISO8601 parsing routine should understand both.
1999-01-31T00:00:00.000Z is a different point in time. If you really want a string in local time, you can convert directly:
localISO = Format(localDate,"yyyy-mm-ddThh:mm:ss")dss010101 commentedon Apr 10, 2024
Thanks much. yes i need it in local time w/o the timezone. i need it to match the daily eod data i pull from Bloomberg, which comes in like this '2022-02-01 00:00:00.000" from what i see.
Nick-vanGemeren commentedon Apr 10, 2024
The string that you show from Bloomberg is not an ISO8601 string (no T) and has no time zone indication (so presumed your local time). Is that what you see in the raw data or has it been massaged by a JSON converter or Bloomberg wrapper …? Is that an actual String value in VBA or a display of some other variable type?
I can’t see a problem here. Maybe post some code and data and describe what unexpected results you get?
'2022-02-01 00:00:00.000" would be a ‘end of local day’ for 31 Jan 2022, not 31 Jan 1999.
dss010101 commentedon Apr 10, 2024
The data in the json receieved from the bbg request looks like this:
So reading and loading to a pandas dataframe it ends up without a timezone. Im fine with that - as i can assume the source data is EST locale.
In excel, i allow override of this data. if the date in the excel column is "1/31/1999", after conversion using ConvertToIso, it ends up with a timezone part like this
1999-01-31T05:00:00.000ZIf i upload that to the database like this, i would end up with functionally with two duplicate 'DATE' since its effectively two datetimestamps. so thats the problem im trying to solve. I would prefer solving it by massaging or coercing the data from the client excel, and leaving the backend as is to support flexibility of storing dates + timestamped data.
Nick-vanGemeren commentedon Apr 10, 2024
We'll forgive you the backtick problems. Use the Preview tab to check formatting before posting.
I am not familiar with the Bloomberg interface or 'pandas dataframe'.
There are two datetimes in that JSON. I think you are referring by 'it' to the DATE value. The DL_SNAPSHOT_START_TIME is probably not relevant. I don't immediately see where you get two datetimes going to the database.
The DATE value is just a simple date string, not date-time as you wrote.
If you are analysing the JSON with JsonConverter's ParseJson, its (VBA-UTC's) ParseISO function will return the DATE value as a local Date (sidestepping a bug for strings with time but without time zone).
So the Excel cell should have a clean Date value (without fraction). It's a bit weird to allow users to modify historical data, but ... OK as long as they only input a valid date.
The cell is then exported to the database as a text string. The database can probably accept several formats which it will convert to its internal format. A tolerant database could accept any of these as equivalents (
Withthe cell Range):If the database does not handle the field as a datetime, you should get that corrected.
Please explain this and show the practical results.
dss010101 commentedon Apr 10, 2024
Ok let me step back and explain this more clearly as i think there is some confusion here.
There are two parts to the process. A nightly python process that uses the bloomberg api to fetch data for some securities and upsert(insert or update) those into a timeseries table in the database.
The 2nd part is an excel spreadsheet that allows a user to specify and update their own data for a security. this data is read from the excel spreadsheet, converted to json using the JsonConverter library and posted to a backend webservice to upsert (insert or update) into the database's timeseries table, with key being security and date.
Effectively, this allows a user to overwrite the data downloaded if they like.
an example of data uploaded from bloomberg would insert a date that when queried in the database, would show like this this:
2022-01-31 00:00:00.000on the other hand, data posted from the excel spreadsheet, as mentioned is converted to json using the JsonConverter library and when received by the backend python webservice looks like this in the json:
2006-01-31T05:00:00Zand when inserted and queried in the database, looks like this:2022-01-31 05:00:00.000So now i have two rows for ticker XYZ with the same date, but different time:
So now when i ask for data for 2022-01-31, there are two records instead of 1. IN the above example, i can solve if i can make sure the data posted from excel does not have the timezone part added or set it such that it's '00:00:00.000'. if that is the case, the upsert function will update and not insert, since a record with the same ticker and date value exists already.
Nick-vanGemeren commentedon Apr 11, 2024
From that explanation, it's clear that the time zone info is being lost by or after the backend web service. Either the service is badly interpreting the JSON (and not even raising an error on a string it doesn't understand) or there is a problem in the update process. Fixing that will be better than trying to work around the bug(s).
You should also check the application design will still give sensible results if some users move to Chicago (CDT) or elsewhere. This could avert a crisis later.
Nick-vanGemeren commentedon Apr 12, 2024
If you really can't wait for the web service to be fixed, I've found a temporary workaround:
When you are building the Dictionary that you will convert to JSON, instead of copying the Date value, format it into the local ISO datetime string. No ConvertToIso call will be needed.
dss010101 commentedon Apr 13, 2024
There isnt an issue with the backend really. its doing the right thing. i just need the front end to NOT pass the timezone part or be able to generate a json where time is in EDT/EST instead of UTC.
your solution is the that's the crux of the problem. im using the JsonCOnverter library and it doesnt give me an option not to use ConvertToIso or to specify the timezone i want to use. it just does it. so id have to change code in that libarary, whcih would effectively mean id have to maintain it going forth. not something i want to do.
for now on the back-end im just forcing a conversion to EDT. its a bit of a hack
Nick-vanGemeren commentedon Apr 14, 2024
You definitely do not need to change JsonConverter. I've given aworkaround, but I think fixing the web service is better. Upgrading to a database which understands international time would be even better.
Another approach is to follow Bloomberg and stop defining the DATE as a point in time, but more a 'market day' or 'overnight run day identifier'. You can then just transfer a 'yyyy-mm-dd' string (using the method of the workaround). The user's local date time doesn't get involved.
dss010101 commentedon Apr 30, 2024
ultimately this does seem to be an odd issue with JsonConverter.ConvertToJson...when used in VBA, some dates are converted as offset -500 and other as -400, i dont now why.
This causes a different of whether data ends up as EST 12 midnight or EST 11:00PM in the database
The solution you provided means i would have to pre-process the data prior to sending over to using JsonConverter.
Or i would have to change JsonConverter itself.
houghtonap commentedon Apr 30, 2024
More than likely the reason why some dates converted with -4:00 and some with -5:00 has to do with whether the date fell in EDT (Eastern Day savings Time) or EST (Eastern Standard Time). You might consider consulting Wikipedia on how date time zones work for a better understanding.
dss010101 commentedon Apr 30, 2024
and why would excel decide on one or the other from the same laptop in the same region/state/city/time?
the underlying is a date field w/ no time attributes
Nick-vanGemeren commentedon Apr 30, 2024
Excel/VBA does not know your time zone. The regional settings for your operating system determine the time zone. System calls are provided for conversion of local date-times to UTC or other time zones.
VBA-UTC is a little lazy and uses system calls which only apply the current algorithm for daylight saving time. New York has been using the same algorithm for many years: EST->EDT second Sunday of March, EDT->EST first Sunday of November. So your results for 2015 are correct since they bridge the change on Nov 1, 2015 02:00.
Your web service should also use system calls to convert back to local time, if your database can't accept UTC time.
See my previous posts about getting round this 'point in time' issue.
dss010101 commentedon Apr 30, 2024
The database can handle utc. its timescaledb and is home to over 20mill rows of data from bloomberg, most through nightly python loader.
This data however comes from an excel spreadsheet - either for back filling purposes or custom calculations.
You said something interesting...the data im uploading for testing is month end only data..i wonder if based on the month, EST or EDT is being applied. I bet that's the issue...
Nick-vanGemeren commentedon May 1, 2024
New York changed algorithm in spring 2007. The old one was: EST->EDT first Sunday of April, EDT->EST last Sunday of October. It seems likely that VBA-UTC (under Windows) will convert incorrectly date-times before 2007 and in the weeks near the start of April and November. On Mac a different conversion method ias used, which may function correctly.
mohawk2 commentedon Jun 20, 2024
So after all these digressions, the original proposal of a backward-compatible enhancement to the specific function mentioned in the OP has still not been implemented. Is there a reason for that?