Why does outlook.timezones.converttime get the DST change wrong?-CodePudding

I am using the following VBA code in Excel:

Function tzAtotzB(srcDT As Date, srcTZ As String, dstTZ As String) As Date
    Dim OutlookApp As Object
    Dim TZones As TimeZones
    Dim convertedTime As Date
    Dim sourceTZ As TimeZone
    Dim destTZ As TimeZone
    Dim secNum As Integer
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set TZones = OutlookApp.TimeZones
    Set destTZ = TZones.Item(dstTZ)
    Set sourceTZ = TZones.Item(srcTZ)
    
    tzAtotzB = TZones.ConvertTime(srcDT, sourceTZ, destTZ)

End Function

Function CETtoUTC(srcDT As Date) As Date
    CETtoUTC = tzAtotzB(srcDT, "Central Europe Standard Time", "UTC")
End Function

Function UTCtoCET(srcDT As Date) As Date
    UTCtoCET = tzAtotzB(srcDT, "UTC", "Central Europe Standard Time")
End Function

For some reason the results are not what one would expect:

Debug.Print UTCtoCET("26/03/2022 23:00:00")
27/03/2022
Debug.Print UTCtoCET("27/03/2022 00:00:00")
27/03/2022 02:00:00 
Debug.Print CETtoUTC("27/03/2022 02:00:00")
27/03/2022 
Debug.Print UTCtoCET("28/03/2021 00:00:00")
28/03/2021 02:00:00

CET is supposed to switch from UTC 1 to UTC 2 starting at 01:00 UTC on the last Sunday of March (source), but the output above shows a switch from UTC 1 to UTC 2 at 00:00 UTC, which is plain incorrect.

I don't see anything wrong with my code. Is this a known bug within Microsoft's codebase?

CodePudding user response：

This appears to be a bug in the underlying implementation of the TimeZones.ConvertTime method in the Outlook VBA Object Model.

Using the code in your question, I was able to reproduce the inaccurate results in VBA after referencing the "Microsoft Outlook 16.0 Object Library". The OutlookApplication.Version is 16.0.0.14931.

The correct results come through in a .NET application running on the same Windows machine using the conversion methods on the .NET TimeZoneInfo object. That uses the time zone data from the Windows Registry, which is the same data that the Outlook VBA library uses, as far as I know.

Here's the .NET C# code that demonstrates the correct results:

var zone = TimeZoneInfo.FindSystemTimeZoneById("Central Europe Standard Time");

var input1 = DateTimeOffset.Parse("2022-03-27T00:00:00Z");
var output1 = TimeZoneInfo.ConvertTime(input1, zone);
Console.WriteLine($"{input1.UtcDateTime:yyyy-MM-dd'T'HH:mm:ssK} => {output1:yyyy-MM-dd'T'HH:mm:sszzz}");

var input2 = DateTimeOffset.Parse("2022-03-27T01:00:00Z");
var output2 = TimeZoneInfo.ConvertTime(input2, zone);
Console.WriteLine($"{input2.UtcDateTime:yyyy-MM-dd'T'HH:mm:ssK} => {output2:yyyy-MM-dd'T'HH:mm:sszzz}");

Which outputs:

2022-03-27T00:00:00Z => 2022-03-27T01:00:00 01:00
2022-03-27T01:00:00Z => 2022-03-27T03:00:00 02:00

That shows the correct transition, so the data is correct. Thus the problem must be specific to the Outlook VBA implementation. I suggest opening a support issue with Microsoft if this is important to you. You can reference this answer.