Pearson Software Consulting Services

    Get/Set File Times

         Neither Excel nor VBA provide a mechanism for retrieving or changing the file times of a file. The three file times are Create Time, Last Access Time (read), and Last Modified Time (write).  This page describes three VBA procedures, GetFileDateTime, GetFileDateTimeAsFILETIME, and SetFileDateTime, that you can use retrieve or set the times associated with a file. Times associated with a file are stored by the system in Greenwich Mean Time (GMT), also called Universal Coordinated Time (UTC). The GetFileTime and SetFileTime Windows API functions, which are used in these procedures,  retrieve and set the file times as GMT times. Therefore it is necessary to use additional Windows API functions to convert the FileTime value of a file's times to the local time zone. The procedures below do this conversion for you. For procedures that convert between GMT and Local Time in general, see the Converting Between GMT And Local Times page.

Note: In this documentation, we will use the terms "Date" and "Time" interchangeably. For example, the term "Create Date" should be understood to refer to both the date and time of the file's creation.  If we need to refer to only the file's Date (without a time component) or the file's Time (without a date component) that distinction will be made clear in the documentation, but in general, both "Date" and "Time" should be interpreted to mean both the date and the time.

It should be noted that the accuracy of the file times depends upon the File System of the drive on which the file resides. For example, on a Windows NT FAT system, the create time has a resolution of 10 milliseconds, while the last modified time has a resolution of about two seconds, and the last access time has a resolution of one full day. On NTFS file systems, the access time has a resolution of about 1 hour.


VB/VBA And Windows Time Formats

VB/VBA and Windows use three date/time formats when working with times.

FILETIME
The first is a FILETIME structure or Type. This type contains two Long 32-bit Integers, creating a 64-bit integer. The value of this 64-bit integer is the number of 100-nanosecond intervals elapsed since 1-January-1601. The definition of FILETIME is shown below:

Public Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

The FILETIME components, when taken together, form a 64-bit Integer, a data type that is not supported in VB/VBA. The system treats the components as UNSIGNED 32-bit Longs, and VB/VBA doesn't support unsigned variables. Therefore, if you need to change a FILETIME, or if you want to view the value of a FILETIME in a readable and meaningful format, use one of the conversion routines, such as FileTimeToSerialTime, that are included in the modTimeConversions module, available as a individual bas module here or in the complete workbook here.

You can discern no meaningful information by examining the contents of a FILETIME directly.  You should not use the FILETIME values to determine if one date is earlier or later than another date. Instead, you should convert the FILETIMEs to a Serial Times using FileTimeToSerialTime  in the modTimeConversions module and compare the Serial Times.

Don't attempt to do arithmetic on a FILETIME value. Instead, convert it to either a Serial Date using FileTimeToSerialTime  in the modTimeConversions module or a SYSTEMTIME using the function Windows API function FileTimeToSystemTime, documented on MSDN, make your adjustments to the Serial Date or SYSTEMTIME structure, and convert that back to a FILETIME using the SerialTimeToFileTime in the modTimeConversions module or with the SystemTimeToFileTime Windows API function. The SystemTimeToFileTime is declared as follows:

Private Declare Function SystemTimeToFileTime Lib "kernel32" _
    (lpSystemTime As SYSTEMTIME, _
    lpFileTime As FILETIME) As Long

 

SYSTEMTIME
The second time format is called SYSTEMTIME, which is a structure with an element for year, date, month, etc  The definition of SYSTEMTIME is shown below:

Public Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type
Note that the wDayOfWeek element of SYSTEMTIME is based on Sunday = 0 through Saturday = 6. This differs from the Weekday VB function, so you must subtract 1 from the result of the Weekday function when setting the wDayOfWeek element, or add 1 to the value of wDayOfWeek when retrieving the wDayOfWeek element.

You can convert a SYSTEMTIME Type to a Serial date with the SystemTimeToSerialTime function in the modTimeConversions module, or to a  FILETIME structure with the SystemTimeToFileTime Windows API function, documented on MSDN.

The system supports SYSTEMTIMES with Day value greater than the number of days in the given month. For example, if you set the wMonth value to 1 (January) and the wDay value to 36, the system will successfully convert this to 5-February. Similarly, the system supports a wMonth value greater than 12. If you set wMonth to 15, it will properly be converted to March of the next year.

SERIAL DATE/TIME
The third and final time variable is most familiar to VB/VBA programmers. This is the Double Serial Date/Time. A Serial Date is a Double or Date type variable. The integer portion (to the left of the decimal point) of this value represents the number of days since 30-December-1900 (1 = 31-December-1900, up through 39074 = 23-December-2006), and the fractional portion (to the right of the decimal) of the value is the fraction of a 24-hour day (06:00:00 = 0.25, 12:00:00 = 0.5, 18:00:00 = 0.75, etc). For example, 23-December-2006 06:00:00 is stored as 39074.25.

System File Times
The system stores file times as FILETIMEs, and the result of the GetFileTime and SetFileTime Windows API functions use the FILETIME format, and store these times as GMT time values. The input and output of the SetFileDateTime and GetFileDateTime functions in the modGetSetFileTime module are in the VB/VBA Serial Date format. These functions adjust the result of GetFileTime and SetFileTime from GMT to Local Time and take Daylight Savings Time into account with Local Times (GMT does not observe Daylight Savings Time).

Both GetFileDateTime and SetFileDateTime can be used to read or write the Create Date, Last Access Date, and  Last Modified Date. You specify which date to read or write using an enum variable called FileDateToProcess. This enum is shown below:

Public Enum FileDateToProcess
    FileDateCreate = 1
    FileDateLastAccess = 2
    FileDateLastModified = 3
End Enum

You specify one of these values to indicate which file time you want to set or retrieve.

Downloads:
The downloadable module zip file contains two modules: modGetSetFileTimes, and modTimeConversionFunctions. Both modGetSetFileTimes and modTimeConversions are required to use the function described on this page. 

Properly Using The Type Declarations:
As a general rule, the Type declarations occur once as Public declarations. If you have the Type declarations as Private in two or more modules and your calling and called procedures are in different modules, you will get a compiler error "ByRef argument type mismatch", even though the declarations are identical. You can remedy this by declaring the types as Public Types only once, and remove all Private declarations from your modules.

 

Functions For Retrieving Or Setting File Times

The code for the GetFileDateTime, GetFileDateTimeAsFILETIME and SetFileDateTime is too lengthy to neatly display on this web page.  Altogether, the code totals about 1500 lines. The functions definitions and example code is included below. To get the actual VBA code, download the bas modules or the complete workbook.

GetFileDateTime
The function GetFileDateTime is used to retrieve the Create Date, Last Access Date, or Last Modified Date of a specified file.  The return value is in Serial format.

Public Function GetFileDateTime(FileName As String, _
        WhichDateToGet As FileDateToProcess, _
        Optional NoGMTConvert As Boolean = False) As Double

where FileName is the name of the file to query and WhichDateToGet is a value indicating which date (Create Date, Last Access Date, Last Modified Date) is to be retrieved. The value of WhichDateToGet is one of the elements of the FIleDateToProcess enum.  The NoGMTConvert parameter indicates whether the file's time is converted from GMT (the stored value returned by the GetFileTime API function) to local time. If this value is omitted or False, the file's GMT time is converted to the local time of day.  If NoGMTConvert is True, the GMT time returned by the GetFileTime API function is  not converted from GMT to Local time. Because Windows converts GMT to Local time when it displays a file's time, the displayed time will be N hours earlier (or later if you are East of GMT) than the value returned by this function. It is recommended that you NOT set NoGMTConvert to TRUE.  In nearly all circumstances, you will want this parameter to be False.

Public Enum FileDateToProcess
    FileDateCreate = 1
    FileDateLastAccess = 2
    FileDateLastModified = 3
End Enum

GetFileDateTime will return the VB/VBA date/time Double variable, or -1 if an error occurs. Possible errors include passing the name of a non-existent file or a invalid value for WhichDateToGet. The result time is adjusted to reflect Daylight Savings Time.

And example of GetFileDateTime is shown below:

Sub TestGetFileDateTime()
Dim FName As String
Dim Result As Boolean
Dim TheNewTime As Double
Dim WhatTime As FileDateToProcess
'''''''''''''''''''''''''''''
' Set the paramaeters to 
' GetFileDateTime.
'''''''''''''''''''''''''''''
FName = "C:\Folder\File.txt"
WhatTime = FileDateCreate

TheNewTime = GetFileDateTime(FileName:=FName, WhichDateToGet:=WhatTime)
If TheNewTime < 0 Then
    Debug.Print "An error occurred in GetFileDateTime"
Else
    Debug.Print "File Date/Time is to: " & Format(TheNewTime, "dd-mmm-yyyy hh:mm:ss")
End If
End Sub

GetFileDateTimeAsFILETIME
The GetFileTimeAsFILETIME function populates a FILETIME variable with the requested file time of a specified file.

Public Function GetFileDateTimeAsFILETIME(FileName As String, _
  WhichDateToGet As FileDateToProcess, FTime As FILETIME, _
  Optional ConvertFromGMT As Boolean = False) As Boolean

where FileName is the name of the file whose time is to be retrieved, WhichDateToGet indicates which date to retrieve (Create, Access, or Modify), FTime is a FILETIME variable that will be populated with the requested file time, and ConvertFromGMT indicates whether the procedure should convert the file time from GMT to Local Time. Example of GetFileTimeAsFILETIME:

Dim DateSerial As Date
Dim FT As FILETIME
Dim FileName As String
Dim ConvertFromGMT As Boolean
Dim Res As Boolean

FileName = "C:\Test.txt"
ConvertFromGMT = True ' False = Do NOT convert from GMT to Local. True = Convert from GMT To Local
Res = GetFileTimeAsFILETIME(FileName:=FileName, _
    WhichDateToGet:=FileDateLastModified, _
    FTime:=FT, _
    ConvertFromGMT:=ConvertFromGMT)
If Res = True Then
    ''''''''''''''''''''''''''''
    ' Success. Convert FT to
    ' a serial date for display.
    ''''''''''''''''''''''''''''
    DateSerial = FileTimeToSerialTime(FileTimeValue:=FT)
    Debug.Print "FILETIME Low: " & Hex(FT.dwLowDateTime) & _
        " High: " & Hex(FT.dwHighDateTime) & _
        " Serial: " & Format(DateSerial, "dd-mmm-yyyy hh:mm:ss")
Else
    Debug.Print "An error occurred in GetFileTimeAsFILETIME."
End If


SetFileDateTime
The function SetFileDateTime is used to set the Create Date, Last Access Date, or Last Modified Date of a specified file.

    Public Function SetFileDateTime(FileName As String, _
        FileDateTime As Double, WhichDateToChange As FileDateToProcess, _
        Optional NoGMTConvert As Boolean = False) As Boolean

where FileName is the name of the file to change, FileDateTime is the new time to assign to the file, in standard VB/VBA dddd.tttttt Double variable format, and WhichDateToChange indicates which date/time of the file to change. It is one of the value of the FileDateToProcess enum described above. NoGMTConvert indicates whether FileDateTime should be converted to GMT. Windows stores file times in GMT, and there in nearly all circumstances you should set NoGMTConvert to False. Since Windows displays file times in local time format, the displayed file time will be N hours later (or earlier if you are East of GMT)  than the time passed in as FileDateTime.

Dim FName As String
Dim Result As Boolean
Dim TheNewTime As Double
Dim WhatTime As FileDateToProcess
Dim TheNewDate As Double

'''''''''''''''''''''''''''''''
' Craete the new date and time.
'''''''''''''''''''''''''''''''
TheNewDate = DateSerial(2006, 7, 4)
TheNewDate  = TheNewDate + TimeSerial(12, 34, 56)

FName = "C:\Test.txt"

Result = SetFileDateTime(FileName:=FName, FileDateTime:=TheNewDate, _ 
                         WhichDateToChange:=WhatTime, NoGMTConvert:=False)
If Result = True Then
    Debug.Print "File date/time successfully modified."
Else
    Debug.Print "An error occurred with SetFileDateTime."
End If

CompareFileTimes
The CompareFileTimes compares the date and times of two files, and returns -1 if FileName1 has a date earlier than FileName2, 0 if the file dates are equal, or +1 if FileName1 has a date later than FileName2. It returns NULL if an error occurred:

Public Function CompareFileTimes(FileName1 As String, FileName2 As String, _
    WhichDate As FileDateToProcess) As Variant

For example,

Dim Res As Variant
Res = CompareFileTimes(FileName1:="C:\Test1.txt", FileName2:="C:\Test2.txt", _
    WhichDate:=FileDateCreate)
If IsNull(Res) = True Then
    Debug.Print "An error occurred in CompareFileTimes"
Else
    If Res < 0 Then
        Debug.Print "File1 is earlier than File2"
    Else
        Debug.Print "File1 is later than File2"
    End If
End If

You can download the bas module files here or a complete workbook with examples here.

 

 

     
     

 

 Created By Chip Pearson and Pearson Software Consulting, LLC 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson