VBA leap of 1 hour in modify file information

Question

I use this code to read the last modified date of a file:

Set oFS = CreateObject("Scripting.FileSystemObject")

Cells(i + 1, 37) = oFS.GetFile(dir_f_file).Datelastmodified

in a file changed in February, in the explorer I have the date of 7/2/2020 11:57 and in excel it appears 07/02/2020 12:57

in the file changed in April I have the dates 13/04/2020 15:28 in excel is the same

what could be the problem and how can I fix this error excel result

Answer 1

I had the same issue, but could not find a good answer. By combining various answers I got to this simple answer:

Function fDtCorrectedForDaylightSaving(ByVal pDateTime As Date) As Date
    ' -----------------------------------------------------------------------------------
    ' Calculates the Daylight Saving offset; use to get the actual date/time of a file
    ' -----------------------------------------------------------------------------------
    Dim tDtObj As Object, tUTC As Date, tDtLocal As Date, tDayLightOffset As Date
    Const cTIME_ZONE_OFFSET As Date = 13 / 24 ' Auckland = GMT +13 hrs
    
    Set tDtObj = CreateObject("WbemScripting.SWbemDateTime")
    tDtObj.setvardate pDateTime
    
    tDtLocal = tDtObj.getvardate(True)
    tUTC = tDtObj.getvardate(False)
    tDayLightOffset = tUTC - tDtLocal + cTIME_ZONE_OFFSET
    fDtCorrectedForDaylightSaving = pDateTime - tDayLightOffset
    
End Function

Thus in the code of the person raising the question the change would be:

Cells(i + 1, 37) = fDtCorrectedForDaylightSaving( _ 
                       oFS.GetFile(dir_f_file).Datelastmodified)