title: Getting file last modified date (explorer value not cmd value)
body: <p>I have written some Excel VBA code to add the filenames, versions, and last modified date/time to a worksheet. The code appears to work fine, except sometimes the time portion of the <code>Last Modified Date</code> for a file will either be exactly 1 hour forward or backward from what I see in an Explorer window.</p>
  
  <p>I have noticed the values that my code returns is the same as the modified date/time shown in a cmd window if I perform a <code>dir</code> command.</p>
  
  <p>For example, if I look up the <strong>dbghelp.dll</strong> file in the <strong>system32</strong> folder:</p>
  
  <pre><code> C:\Windows\System32&gt;dir dbghelp.*
   Volume in drive C has no label.
   Volume Serial Number is 16E8-4159
  
   Directory of C:\Windows\System32
  
   21/11/2010  04:24         1,087,488 dbghelp.dll
                 1 File(s)      1,087,488 bytes
                 0 Dir(s)  60,439,101,440 bytes free
  
   C:\Windows\System32&gt;
  </code></pre>
  
  <p>But the same file in an Explorer window shows a modified time of <strong>03:24 on 21/11/2010</strong> - 1 hour earlier.</p>
  
  <p>The code I have written is returning the cmd window time, whereas I want the Explorer window time:</p>
  
  <pre><code>    Sub GetFileDetails()
      Dim path As String
      Dim objFSO As Object
      Dim objFile As Object
      Dim objFolder As Object
      Dim loopCount As Integer
      Dim pathCheck As Boolean
  
  
      'Prompt for directory path
      path = InputBox(Prompt:="Enter file path", Title:="Enter file path", Default:="")
      If (path = "" Or path = vbNullString) Then
          MsgBox ("Invalid path - exiting")
          Exit Sub
      End If
  
      'Required for interacting with filesystem
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objFolder = objFSO.GetFolder(path)
  
      '1st row for path title, 2nd row for column headings
      loopCount = 3
      For Each objFile In objFolder.Files
          Range("A" &amp; loopCount).Value = objFile.Name
          Range("B" &amp; loopCount).Value = objFSO.GetFileVersion(objFile)
          Range("C" &amp; loopCount).Value = objFile.DateLastModified
  
          'Combine Version and Modified
          If Range("B" &amp; loopCount).Value &lt;&gt; "" Then
              Range("D" &amp; loopCount).Value = Range("B" &amp; loopCount).Value &amp; ", " &amp; Range("C" &amp; loopCount).Value
          Else
              Range("D" &amp; loopCount).Value = Range("C" &amp; loopCount).Value
          End If
  
          loopCount = loopCount + 1
      Next
  
      'Set up headings
      Range("A" &amp; 1).Value = (loopCount - 3) &amp; " files found in " &amp; path
      Range("A" &amp; 2).Value = "FileName"
      Range("B" &amp; 2).Value = "Version"
      Range("C" &amp; 2).Value = "Modified"
      Range("D" &amp; 2).Value = "Version &amp; Modified"
  End Sub
  </code></pre>
  
  <p>If anyone can shed some light on this issue - it will be greatly appreciated.</p>
  
  <p>===EDIT===
  This is the code I have come up with which always gives me the same time as displayed in an explorer window:</p>
  
  <pre><code>Sub GetFileDetails()
      Dim path As String
      Dim objFSO As Object
      Dim objFile As Object
      Dim objFolder As Object
      Dim loopCount As Integer
      Dim pathCheck As Boolean
  
      Dim modDate As Date
      Dim modHour As Integer
      Dim modMin As Integer
  
      'Prompt for directory path
      path = InputBox(Prompt:="Enter file path", Title:="Enter file path", Default:="")
      If (path = "" Or path = vbNullString) Then
          MsgBox ("Invalid path - exiting")
          Exit Sub
      End If
  
      'Required for interacting with filesystem
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objFolder = objFSO.GetFolder(path)
  
      '1st row for path title, 2nd row for column headings
      loopCount = 3
      For Each objFile In objFolder.Files
          Range("A" &amp; loopCount).Value = objFile.Name
          Range("B" &amp; loopCount).Value = objFSO.GetFileVersion(objFile)
          Range("D" &amp; loopCount).Value = objFile.Name
  
  
          'The date modified time for files made in Summer Time are correct, whereas Winter Time will be 1 hour forward
          If (IsItSummerTime(objFile.DateLastModified) = True) Then
              Range("C" &amp; loopCount).Value = objFile.DateLastModified
          Else
              modDate = Format(objFile.DateLastModified, "DD-MM-YYYY")
              modHour = Hour(objFile.DateLastModified)
              modMin = Minute(objFile.DateLastModified)
  
              modHour = modHour - 1
  
              If (modHour &lt; 10) Then
                 If (modMin &lt; 10) Then
                    Range("C" &amp; loopCount).Value = modDate &amp; " 0" &amp; modHour &amp; ":0" &amp; modMin
                 Else
                    Range("C" &amp; loopCount).Value = modDate &amp; " 0" &amp; modHour &amp; ":" &amp; modMin
                 End If
              Else
                 If (modMin &lt; 10) Then
                    Range("C" &amp; loopCount).Value = modDate &amp; " " &amp; modHour &amp; ":0" &amp; modMin
                 Else
                    Range("C" &amp; loopCount).Value = modDate &amp; " " &amp; modHour &amp; ":" &amp; modMin
                 End If
              End If
          End If
  
          'Combine Version and Modified
          If Range("B" &amp; loopCount).Value &lt;&gt; "" Then
              Range("E" &amp; loopCount).Value = Range("B" &amp; loopCount).Value &amp; ", " &amp; Range("C" &amp; loopCount).Value
          Else
              Range("E" &amp; loopCount).Value = Range("C" &amp; loopCount).Value
          End If
  
          loopCount = loopCount + 1
      Next
  
      'Set up headings
      Range("A" &amp; 1).Value = (loopCount - 3) &amp; " files found in " &amp; path
      Range("A" &amp; 2).Value = "FileName"
      Range("B" &amp; 2).Value = "Version"
      Range("C" &amp; 2).Value = "Modified"
      Range("D" &amp; 2).Value = "FileName"
      Range("E" &amp; 2).Value = "Version &amp; Modified"
  
  End Sub
  
  Function IsItSummerTime(inDate As Date) As Boolean
      Dim inDateYear As Integer
      Dim findFirstSunday As Date
      Dim firstSundayDate As Date
      Dim startDays As Integer
      Dim endDays As Integer
      Dim summerStart As Date
      Dim summerEnd As Date
  
      'Summer Time starts on the 13th week
      'Summer Time ends on the 42nd week
      If (IsItALeapYear(inDate) = True) Then
          startDays = (12 * 7) + 1
          endDays = (42 * 7) + 1
      Else
          startDays = 12 * 7
          endDays = 42 * 7
      End If
  
      'Find the date of the first Sunday in the year
      inDateYear = Year(inDate)
      For i = 1 To 7
          findFirstSunday = DateSerial(inDateYear, 1, i)
          If (Weekday(findFirstSunday) = 1) Then
              firstSundayDate = findFirstSunday
          End If
      Next i
  
      'Calculate the start and end dates for Summer Time
      summerStart = firstSundayDate + startDays
      summerEnd = firstSundayDate + endDays
  
      'Compare inDate to Summer Time values and return boolean value
      If (inDate &gt;= summerStart And inDate &lt; summerEnd) Then
          IsItSummerTime = True
      Else
          IsItSummerTime = False
      End If
  End Function
  Function IsItALeapYear(inDate As Date) As Boolean
      If (Month(DateSerial(Year(inDate), 2, 29))) = 2 Then
          IsItALeapYear = True
      Else
          IsItALeapYear = False
      End If
  End Function
  </code></pre>