How to Use FileDateTime Function in VBA (With Example)

You can use the FileDateTime function in VBA to return the date and time when a file was created or last modified.

Here is one common way to use this function in practice:

Sub CheckLastModify()

    Dim wb_name As String
    
    wb_name = InputBox("Please enter the workbook name:")
    
    MsgBox FileDateTime(wb_name)
    
End Sub

When this macro is run, an input box will appear where a user can type in the name of an Excel workbook.

The macro will then produce a message box that contains the date and time when the particular workbook was created or last modified.

The following example shows how to use this syntax in practice.

Example: How to Use FileDateTime Function in VBA

Suppose we have an Excel workbook called My_Workbook.xlsx located in the following location:

C:\Users\bob\Documents\my_workbook.xlsx

Suppose we would like to check when this workbook was last created or modified.

We can create the following macro to do so:

Sub CheckLastModify()

    Dim wb_name As String
    
    wb_name = InputBox("Please enter the workbook name:")
    
    MsgBox FileDateTime(wb_name)
    
End Sub

Once we run this macro, a box will appear where I can type in the path to the workbook in the input box:

Once I click OK, the macro will produce the following message box:

The macro tells us that the workbook was last modified on 7/28/2023 at 9:27:01 AM.

Note that if you only want the date the file was modified then you can wrap the FileDateTime function with the DateValue function as follows:

Sub CheckLastModify()

    Dim wb_name As String
    
    wb_name = InputBox("Please enter the workbook name:")
    
    MsgBox DateValue(FileDateTime(wb_name))
    
End Sub

Now when you run the macro and type in the file path, the message box will only display the date the field was last modified without the time: