Difference in hours between two dates on daylight saving time with VBA-CodePudding

Last Sunday it had 23 hours as a result of the damn daylight saving time change, as a result of it, some of my daily calculations are incorrect because they are made with an assumption of 24 hours that is not fulfilled that day, I cannot find the method of calculate those 23 hours (25 in the case of the winter time change), I have searched without finding anything that helps me, any idea how to solve the problem? I've already run out.

CodePudding user response：

You can use the timezone function BiasWindowsTimezone in my project at GitHub:

VBA.Timezone-Windows

(module WtziCore) to obtain the bias values for the start and the end time taking DST in account. Then, add the bias values to both date values and calculate the difference in hours:

Public Function HoursNormal( _
    ByVal Date1 As Date, _
    ByVal Date2 As Date, _
    ByVal Timezone As String) _
    As Long

    Dim Date1Normal As Date
    Dim Date2Normal As Date
    Dim Hours       As Long
    
    Date1Normal = DateAdd("n", BiasWindowsTimezone(Timezone, True, Date1), Date1)
    Date2Normal = DateAdd("n", BiasWindowsTimezone(Timezone, True, Date2), Date2)
    
    Hours = DateDiff("h", Date1Normal, Date2Normal)
    
    HoursNormal = Hours

End Function

The timezone must be the literal of yours as found in the Windows Registry, for example W. Europe (there is code as well to retrieve and list these), and using your Sunday date, you will get 23 hours:

Date1 = #2022-03-27 00:00#
Date2 = #2022-03-28 00:00#

? HoursNormal(Date1, Date2, "W. Europe")
 23

CodePudding user response：

I have found a solution, maybe not the ideal one, but it solves my need. Returns 23h for March 27th and 25h for Oct 30th dates of the time change. Thank you very much to all.

Function HorasDia(datFecha As Date) As Byte Dim OutlookApp As Object On Error Resume Next

Set OutlookApp = CreateObject("Outlook.Application") HorasDia = DateDiff("h", OutlookApp.TimeZones.ConvertTime(datFecha, OutlookApp.TimeZones.Item("W. Europe Standard Time"), OutlookApp.TimeZones.Item("UTC")), OutlookApp.TimeZones.ConvertTime(datFecha 1, OutlookApp.TimeZones.Item("W. Europe Standard Time"), OutlookApp.TimeZones.Item("UTC")))

If Not OutlookApp Is Nothing Then Set OutlookApp = Nothing End Function