`FileDateTime` Function

Returns a Variant (Date) representing the date and time when a file was created or last modified.

Syntax

FileDateTime(pathname)

Parameters

pathname: Required. A String expression that specifies a file name. May include directory or folder, and drive. If the file is not found, an error occurs.

Return Value

Returns a Variant of subtype Date representing the date and time when the file was last modified. The returned value includes both date and time components.

Remarks

The FileDateTime function returns the last modification date and time of a file. This is useful for file management, backup utilities, synchronization, and determining if files need to be updated.

Important Characteristics:

Returns date/time of last modification
File does not need to be open
Error if file does not exist (Error 53)
Error if path is invalid (Error 76)
Works with full paths and relative paths
Returns same value as file system shows
Precision depends on file system (typically 2-second resolution on FAT, 100ns on NTFS)
Affected by daylight saving time changes
Returns local time (not UTC)
Can be used with wildcards via Dir function

Typical Uses

Determine if file is newer than another
Check if file has been modified since last access
File synchronization between locations
Backup and archive utilities
Change detection systems
File age calculations
Automated cleanup of old files
Build systems (check if recompilation needed)

Examples

Basic Usage

Dim fileDate As Date

' Get file modification date/time
fileDate = FileDateTime("C:\data.txt")
Debug.Print "File last modified: " & fileDate

' Format the date/time
Debug.Print Format(fileDate, "yyyy-mm-dd hh:nn:ss")

Compare File Dates

Function IsFileNewer(file1 As String, file2 As String) As Boolean
' Returns True if file1 is newer than file2
On Error GoTo ErrorHandler

Dim date1 As Date
Dim date2 As Date

date1 = FileDateTime(file1)
date2 = FileDateTime(file2)

IsFileNewer = (date1 > date2)
Exit Function

ErrorHandler:
IsFileNewer = False
End Function

Check If File Was Modified Today

Function IsModifiedToday(filePath As String) As Boolean
On Error GoTo ErrorHandler

Dim fileDate As Date
fileDate = FileDateTime(filePath)

' Compare just the date part
IsModifiedToday = (Int(fileDate) = Int(Date))
Exit Function

ErrorHandler:
IsModifiedToday = False
End Function

Common Patterns

Find Newest File in Directory

Function FindNewestFile(folderPath As String, pattern As String) As String
Dim fileName As String
Dim newestFile As String
Dim newestDate As Date
Dim currentDate As Date

' Ensure path ends with backslash
If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

fileName = Dir(folderPath & pattern)
newestDate = #1/1/1900#

Do While fileName <> ""
On Error Resume Next
currentDate = FileDateTime(folderPath & fileName)

If Err.Number = 0 Then
If currentDate > newestDate Then
newestDate = currentDate
newestFile = fileName
End If
End If

Err.Clear
fileName = Dir
Loop

FindNewestFile = newestFile
End Function

Get File Age

Function GetFileAgeInDays(filePath As String) As Long
On Error GoTo ErrorHandler

Dim fileDate As Date
fileDate = FileDateTime(filePath)

' Calculate difference in days
GetFileAgeInDays = DateDiff("d", fileDate, Now)
Exit Function

ErrorHandler:
GetFileAgeInDays = -1
End Function

Function GetFileAgeInHours(filePath As String) As Long
On Error GoTo ErrorHandler

Dim fileDate As Date
fileDate = FileDateTime(filePath)

GetFileAgeInHours = DateDiff("h", fileDate, Now)
Exit Function

ErrorHandler:
GetFileAgeInHours = -1
End Function

Delete Old Files

Sub DeleteOldFiles(folderPath As String, daysOld As Long)
Dim fileName As String
Dim fullPath As String
Dim fileDate As Date
Dim ageInDays As Long

If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

fileName = Dir(folderPath & "*.*")

Do While fileName <> ""
fullPath = folderPath & fileName

On Error Resume Next
fileDate = FileDateTime(fullPath)

If Err.Number = 0 Then
ageInDays = DateDiff("d", fileDate, Now)

If ageInDays > daysOld Then
Kill fullPath
Debug.Print "Deleted: " & fileName & " (Age: " & ageInDays & " days)"
End If
End If

Err.Clear
fileName = Dir
Loop
End Sub

Check If File Needs Backup

Function NeedsBackup(sourceFile As String, backupFile As String) As Boolean
On Error GoTo ErrorHandler

Dim sourceDate As Date
Dim backupDate As Date

' Get source file date
sourceDate = FileDateTime(sourceFile)

' Check if backup exists
If Dir(backupFile) = "" Then
' No backup exists
NeedsBackup = True
Exit Function
End If

' Get backup file date
backupDate = FileDateTime(backupFile)

' Needs backup if source is newer than backup
NeedsBackup = (sourceDate > backupDate)
Exit Function

ErrorHandler:
NeedsBackup = True  ' Assume needs backup on error
End Function

List Files Modified Within Date Range

Function GetFilesModifiedBetween(folderPath As String, startDate As Date, _
endDate As Date) As Collection
Dim files As New Collection
Dim fileName As String
Dim fullPath As String
Dim fileDate As Date

If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

fileName = Dir(folderPath & "*.*")

Do While fileName <> ""
fullPath = folderPath & fileName

On Error Resume Next
fileDate = FileDateTime(fullPath)

If Err.Number = 0 Then
If fileDate >= startDate And fileDate <= endDate Then
files.Add fullPath
End If
End If

Err.Clear
fileName = Dir
Loop

Set GetFilesModifiedBetween = files
End Function

File Synchronization Check

Function SynchronizeFile(sourceFile As String, destFile As String) As Boolean
On Error GoTo ErrorHandler

Dim sourceDate As Date
Dim destDate As Date
Dim needsCopy As Boolean

sourceDate = FileDateTime(sourceFile)

' Check if destination exists
If Dir(destFile) = "" Then
needsCopy = True
Else
destDate = FileDateTime(destFile)
needsCopy = (sourceDate > destDate)
End If

If needsCopy Then
FileCopy sourceFile, destFile
Debug.Print "Synchronized: " & sourceFile & " -> " & destFile
SynchronizeFile = True
Else
Debug.Print "Already synchronized: " & destFile
SynchronizeFile = False
End If

Exit Function

ErrorHandler:
Debug.Print "Error synchronizing: " & Err.Description
SynchronizeFile = False
End Function

Build System - Check Dependencies

Function SourceNewerThanExecutable(sourceFile As String, exeFile As String) As Boolean
' Used in build systems to determine if recompilation is needed
On Error GoTo ErrorHandler

Dim sourceDate As Date
Dim exeDate As Date

' Check if executable exists
If Dir(exeFile) = "" Then
SourceNewerThanExecutable = True  ' Need to build
Exit Function
End If

sourceDate = FileDateTime(sourceFile)
exeDate = FileDateTime(exeFile)

SourceNewerThanExecutable = (sourceDate > exeDate)
Exit Function

ErrorHandler:
SourceNewerThanExecutable = True  ' Assume needs build on error
End Function

Generate File Report

Sub GenerateFileReport(folderPath As String, reportFile As String)
Dim fileName As String
Dim fullPath As String
Dim fileDate As Date
Dim reportNum As Integer

If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

reportNum = FreeFile
Open reportFile For Output As #reportNum

Print #reportNum, "File Report for: " & folderPath
Print #reportNum, "Generated: " & Format(Now, "yyyy-mm-dd hh:nn:ss")
Print #reportNum, String(80, "-")
Print #reportNum, "Filename", "Modified Date", "Age (Days)"
Print #reportNum, String(80, "-")

fileName = Dir(folderPath & "*.*")

Do While fileName <> ""
fullPath = folderPath & fileName

On Error Resume Next
fileDate = FileDateTime(fullPath)

If Err.Number = 0 Then
Print #reportNum, fileName, _
Format(fileDate, "yyyy-mm-dd hh:nn:ss"), _
DateDiff("d", fileDate, Now)
End If

Err.Clear
fileName = Dir
Loop

Close #reportNum
End Sub

Monitor File Changes

Private lastCheckedDate As Date

Function HasFileChanged(filePath As String) As Boolean
On Error GoTo ErrorHandler

Dim currentDate As Date
currentDate = FileDateTime(filePath)

If lastCheckedDate = 0 Then
' First check
lastCheckedDate = currentDate
HasFileChanged = False
Else
HasFileChanged = (currentDate > lastCheckedDate)
If HasFileChanged Then
lastCheckedDate = currentDate
End If
End If

Exit Function

ErrorHandler:
HasFileChanged = False
End Function

Sort Files by Date

Type FileInfo
Name As String
ModifiedDate As Date
End Type

Function GetFilesSortedByDate(folderPath As String) As Variant
Dim files() As FileInfo
Dim fileName As String
Dim fullPath As String
Dim count As Long
Dim i As Long, j As Long
Dim temp As FileInfo

If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If

' Collect files
ReDim files(0 To 100)
count = 0
fileName = Dir(folderPath & "*.*")

Do While fileName <> ""
fullPath = folderPath & fileName

On Error Resume Next
files(count).Name = fileName
files(count).ModifiedDate = FileDateTime(fullPath)

If Err.Number = 0 Then
count = count + 1
If count > UBound(files) Then
ReDim Preserve files(0 To UBound(files) + 100)
End If
End If

Err.Clear
fileName = Dir
Loop

If count > 0 Then
ReDim Preserve files(0 To count - 1)

' Bubble sort by date (newest first)
For i = 0 To count - 2
For j = i + 1 To count - 1
If files(j).ModifiedDate > files(i).ModifiedDate Then
temp = files(i)
files(i) = files(j)
files(j) = temp
End If
Next j
Next i
End If

GetFilesSortedByDate = files
End Function

Advanced Usage

Incremental Backup System

Function PerformIncrementalBackup(sourceFolder As String, backupFolder As String, _
lastBackupDate As Date) As Long
Dim fileName As String
Dim sourcePath As String
Dim backupPath As String
Dim fileDate As Date
Dim copiedCount As Long

If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
If Right(backupFolder, 1) <> "\" Then backupFolder = backupFolder & "\"

' Ensure backup folder exists
If Dir(backupFolder, vbDirectory) = "" Then
MkDir backupFolder
End If

fileName = Dir(sourceFolder & "*.*")
copiedCount = 0

Do While fileName <> ""
sourcePath = sourceFolder & fileName
backupPath = backupFolder & fileName

On Error Resume Next
fileDate = FileDateTime(sourcePath)

If Err.Number = 0 And fileDate > lastBackupDate Then
FileCopy sourcePath, backupPath
If Err.Number = 0 Then
copiedCount = copiedCount + 1
Debug.Print "Backed up: " & fileName
End If
End If

Err.Clear
fileName = Dir
Loop

PerformIncrementalBackup = copiedCount
End Function

File Cache Invalidation

Private Type CacheEntry
FilePath As String
CachedDate As Date
CachedData As Variant
End Type

Private cache() As CacheEntry
Private cacheCount As Long

Function GetCachedFileData(filePath As String) As Variant
Dim i As Long
Dim currentDate As Date

On Error Resume Next
currentDate = FileDateTime(filePath)

If Err.Number <> 0 Then
GetCachedFileData = Null
Exit Function
End If

' Check if in cache and still valid
For i = 0 To cacheCount - 1
If cache(i).FilePath = filePath Then
If cache(i).CachedDate = currentDate Then
' Cache is valid
GetCachedFileData = cache(i).CachedData
Exit Function
Else
' Cache is stale, reload
cache(i).CachedData = LoadFileData(filePath)
cache(i).CachedDate = currentDate
GetCachedFileData = cache(i).CachedData
Exit Function
End If
End If
Next i

' Not in cache, add it
ReDim Preserve cache(0 To cacheCount)
cache(cacheCount).FilePath = filePath
cache(cacheCount).CachedDate = currentDate
cache(cacheCount).CachedData = LoadFileData(filePath)
GetCachedFileData = cache(cacheCount).CachedData
cacheCount = cacheCount + 1
End Function

Multi-Directory Synchronization

Function SynchronizeFolders(sourceFolder As String, destFolder As String) As Long
Dim fileName As String
Dim sourcePath As String
Dim destPath As String
Dim sourceDate As Date
Dim destDate As Date
Dim syncCount As Long

If Right(sourceFolder, 1) <> "\" Then sourceFolder = sourceFolder & "\"
If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"

' Ensure destination exists
If Dir(destFolder, vbDirectory) = "" Then
MkDir destFolder
End If

fileName = Dir(sourceFolder & "*.*")
syncCount = 0

Do While fileName <> ""
sourcePath = sourceFolder & fileName
destPath = destFolder & fileName

On Error Resume Next
sourceDate = FileDateTime(sourcePath)

If Err.Number = 0 Then
' Check if destination exists
If Dir(destPath) = "" Then
' Destination doesn't exist, copy
FileCopy sourcePath, destPath
syncCount = syncCount + 1
Else
' Check dates
destDate = FileDateTime(destPath)
If sourceDate > destDate Then
' Source is newer, update
FileCopy sourcePath, destPath
syncCount = syncCount + 1
End If
End If
End If

Err.Clear
fileName = Dir
Loop

SynchronizeFolders = syncCount
End Function

Change Detection System

Type ChangeRecord
FilePath As String
PreviousDate As Date
CurrentDate As Date
ChangeType As String  ' "Modified", "Created", "Deleted"
End Type

Function DetectChanges(folderPath As String, baseline() As FileInfo) As Variant
Dim changes() As ChangeRecord
Dim changeCount As Long
Dim fileName As String
Dim fullPath As String
Dim currentDate As Date
Dim i As Long
Dim found As Boolean

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

ReDim changes(0 To 100)
changeCount = 0

' Check current files
fileName = Dir(folderPath & "*.*")

Do While fileName <> ""
fullPath = folderPath & fileName

On Error Resume Next
currentDate = FileDateTime(fullPath)

If Err.Number = 0 Then
found = False

' Check if in baseline
For i = LBound(baseline) To UBound(baseline)
If baseline(i).Name = fileName Then
found = True

' Check if modified
If currentDate > baseline(i).ModifiedDate Then
changes(changeCount).FilePath = fullPath
changes(changeCount).PreviousDate = baseline(i).ModifiedDate
changes(changeCount).CurrentDate = currentDate
changes(changeCount).ChangeType = "Modified"
changeCount = changeCount + 1
End If

Exit For
End If
Next i

' If not found in baseline, it's new
If Not found Then
changes(changeCount).FilePath = fullPath
changes(changeCount).CurrentDate = currentDate
changes(changeCount).ChangeType = "Created"
changeCount = changeCount + 1
End If
End If

Err.Clear
fileName = Dir
Loop

If changeCount > 0 Then
ReDim Preserve changes(0 To changeCount - 1)
DetectChanges = changes
Else
DetectChanges = Array()
End If
End Function

Log File Rotation Based on Date

Sub RotateLogFile(logPath As String, maxAgeDays As Long)
Dim logDate As Date
Dim ageDays As Long
Dim archivePath As String

On Error Resume Next

' Check if log file exists
If Dir(logPath) = "" Then Exit Sub

logDate = FileDateTime(logPath)
ageDays = DateDiff("d", logDate, Now)

If ageDays >= maxAgeDays Then
' Create archive name with date
archivePath = Left(logPath, Len(logPath) - 4) & "_" & _
Format(logDate, "yyyymmdd") & ".log"

' Rename current log to archive
Name logPath As archivePath

Debug.Print "Rotated log file: " & logPath & " -> " & archivePath
End If
End Sub

Error Handling

Function SafeFileDateTime(filePath As String) As Variant
On Error GoTo ErrorHandler

SafeFileDateTime = FileDateTime(filePath)
Exit Function

ErrorHandler:
Select Case Err.Number
Case 53  ' File not found
MsgBox "File not found: " & filePath, vbExclamation
SafeFileDateTime = Null
Case 76  ' Path not found
MsgBox "Path not found: " & filePath, vbExclamation
SafeFileDateTime = Null
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
SafeFileDateTime = Null
End Select
End Function

Common Errors

Error 53 (File not found): The specified file does not exist
Error 76 (Path not found): The specified path is invalid

Performance Considerations

FileDateTime is relatively fast (reads file metadata only)
Does not open the file or read contents
Performance depends on file system and disk speed
Network paths are slower than local paths
Consider caching results if checking same file repeatedly
Use Dir function to check existence before calling FileDateTime

Best Practices

Check File Existence First

' Good - Check existence to avoid error
If Dir(filePath) <> "" Then
fileDate = FileDateTime(filePath)
Else
MsgBox "File not found"
End If

' Or use error handling
On Error Resume Next
fileDate = FileDateTime(filePath)
If Err.Number <> 0 Then
MsgBox "Cannot get file date"
End If
On Error GoTo 0

Use Full Paths

' Good - Use full path for clarity
fileDate = FileDateTime("C:\Projects\data.txt")

' Or build from App.Path
fileDate = FileDateTime(App.Path & "\config.ini")

Comparison with Other Functions

`FileDateTime` vs `Now`

' FileDateTime - Gets file modification date
fileDate = FileDateTime("C:\file.txt")

' Now - Gets current system date/time
currentDate = Now

`FileDateTime` vs `GetAttr`

' FileDateTime - Returns date/time of modification
fileDate = FileDateTime("C:\file.txt")

' GetAttr - Returns file attributes (readonly, hidden, etc.)
attrs = GetAttr("C:\file.txt")

Limitations

Returns modification date only (not creation or access date)
File must exist (cannot get date of deleted files)
Returns local time (not UTC)
Precision limited by file system
Cannot set file date/time (read-only function)
Does not work with directories (use Dir with vbDirectory)
No built-in wildcard support (must use with Dir)

Dir: Returns file names matching a pattern
GetAttr: Returns file attributes
FileLen: Returns file size in bytes
Now: Returns current system date and time
Date: Returns current system date
DateDiff: Calculates difference between two dates
Format: Formats date/time for display

FileDateTime Function