DateValue Function

Returns a Variant (Date) containing the date represented by a string expression.

Syntax

DateValue(date)

Parameters

• date: Required. String expression representing a date from January 1, 100 through December 31, 9999. Can also be any expression that can represent a date, a time, or both a date and time, in that range.

Return Value

Returns a Variant of subtype Date. If the string includes valid time information, it's not returned as part of the date (time is set to midnight). Returns Null if the string cannot be converted to a valid date.

Remarks

The DateValue function is used to convert string representations of dates into actual Date values. It recognizes dates according to the system locale settings.

Important Characteristics:

• Interprets strings according to system locale settings
• Recognizes various date formats (MM/DD/YYYY, Month DD, YYYY, etc.)
• Strips time information if present (returns date portion only)
• Two-digit years: 0-29 → 2000-2029, 30-99 → 1930-1999
• Month names can be spelled out or abbreviated
• Accepts dates in various formats depending on locale
• Returns midnight (00:00:00) for time portion
• Case-insensitive for month names

Recognized Date Formats

DateValue recognizes many formats (locale-dependent):

' Numeric formats
DateValue("1/15/2025")        ' MM/DD/YYYY (US)
DateValue("15/1/2025")        ' DD/MM/YYYY (UK)
DateValue("2025-01-15")       ' ISO format
DateValue("1-15-2025")        ' With dashes

' Text formats
DateValue("January 15, 2025") ' Full month name
DateValue("Jan 15, 2025")     ' Abbreviated month
DateValue("15 January 2025")  ' Different order
DateValue("15-Jan-2025")      ' Mixed format

' Short formats
DateValue("1/15/25")          ' Two-digit year
DateValue("Jan 15")           ' Assumes current year

Examples

Basic Usage

' Convert string to date
Dim birthday As Date
birthday = DateValue("5/15/1990")
MsgBox birthday

' With month name
Dim holiday As Date
holiday = DateValue("December 25, 2025")

' ISO format
Dim isoDate As Date
isoDate = DateValue("2025-01-15")

Parse User Input

Function ParseDate(userInput As String) As Variant
On Error Resume Next
ParseDate = DateValue(userInput)

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

' Usage
Dim inputDate As Variant
inputDate = ParseDate(txtDate.Text)
If IsNull(inputDate) Then
MsgBox "Invalid date format"
End If

Strip Time from DateTime

Function GetDateOnly(dateTime As Variant) As Date
' Convert to string and back to strip time
GetDateOnly = DateValue(CStr(dateTime))
End Function

' Alternative using Int
Function GetDateOnly2(dateTime As Date) As Date
GetDateOnly2 = Int(dateTime)
End Function

Common Patterns

Validate Date String

Function IsValidDateString(dateStr As String) As Boolean
On Error Resume Next
Dim testDate As Date
testDate = DateValue(dateStr)
IsValidDateString = (Err.Number = 0)
End Function

' Usage
If IsValidDateString(txtDate.Text) Then
MsgBox "Valid date"
Else
MsgBox "Invalid date"
End If

Parse Various Formats

Function TryParseDateFormats(dateStr As String) As Variant
Dim formats As Variant
Dim i As Integer

formats = Array("MM/DD/YYYY", "DD/MM/YYYY", "YYYY-MM-DD", _
"Month DD, YYYY", "DD Month YYYY")

On Error Resume Next
TryParseDateFormats = DateValue(dateStr)

If Err.Number = 0 Then Exit Function

' Try with current year if not specified
TryParseDateFormats = DateValue(dateStr & " " & Year(Date))
End Function

Import Data with Date Parsing

Sub ImportDataWithDates(filePath As String)
Dim line As String
Dim fields() As String
Dim recordDate As Date

Open filePath For Input As #1

Do Until EOF(1)
Line Input #1, line
fields = Split(line, ",")

On Error Resume Next
recordDate = DateValue(fields(0))

If Err.Number = 0 Then
' Process valid date record
ProcessRecord recordDate, fields
Else
' Log invalid date
LogError "Invalid date: " & fields(0)
End If

On Error GoTo 0
Loop

Close #1
End Sub

Date Range Validator

Function ValidateDateRange(startStr As String, endStr As String) As Boolean
Dim startDate As Date
Dim endDate As Date

On Error Resume Next
startDate = DateValue(startStr)
If Err.Number <> 0 Then Exit Function

endDate = DateValue(endStr)
If Err.Number <> 0 Then Exit Function

ValidateDateRange = (startDate <= endDate)
End Function

Convert Text File Dates

Function ConvertTextDate(textDate As String) As Date
' Convert various text formats to standard date
ConvertTextDate = DateValue(textDate)
End Function

Sub ProcessLogFile()
Dim logDate As Date
Dim dateStr As String

dateStr = "Jan 15, 2025"
logDate = ConvertTextDate(dateStr)

MsgBox Format(logDate, "yyyy-mm-dd")
End Sub

Flexible Date Parser

Function SmartDateParse(input As String) As Variant
Dim result As Variant

On Error Resume Next

' Try as-is
result = DateValue(input)
If Err.Number = 0 Then
SmartDateParse = result
Exit Function
End If
Err.Clear

' Try adding current year
result = DateValue(input & ", " & Year(Date))
If Err.Number = 0 Then
SmartDateParse = result
Exit Function
End If

' Return Null if unparseable
SmartDateParse = Null
End Function

Database Date Import

Sub ImportDatabaseDates(rs As ADODB.Recordset)
Dim dateField As String
Dim parsedDate As Date

Do Until rs.EOF
dateField = rs("DateField").Value

On Error Resume Next
parsedDate = DateValue(dateField)

If Err.Number = 0 Then
' Update with parsed date
rs("DateField") = parsedDate
rs.Update
End If

rs.MoveNext
Loop
End Sub

Form Date Validation

Function ValidateFormDate(ByRef txt As TextBox, fieldName As String) As Boolean
Dim testDate As Date

On Error Resume Next
testDate = DateValue(txt.Text)

If Err.Number <> 0 Then
MsgBox "Invalid " & fieldName & " format", vbExclamation
txt.SetFocus
ValidateFormDate = False
Else
ValidateFormDate = True
End If
End Function

Advanced Usage

Multi-Locale Date Parser

Function ParseInternationalDate(dateStr As String, locale As String) As Variant
' This is simplified - VB6 doesn't have full locale switching
' Would need Windows API for true locale switching

On Error Resume Next

Select Case UCase(locale)
Case "US"
' Try MM/DD/YYYY first
ParseInternationalDate = DateValue(dateStr)

Case "UK", "EU"
' Parse with assumption of DD/MM/YYYY
' Would need custom parsing logic
ParseInternationalDate = DateValue(dateStr)

Case "ISO"
' YYYY-MM-DD format
ParseInternationalDate = DateValue(dateStr)

Case Else
ParseInternationalDate = DateValue(dateStr)
End Select

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

Batch Date Conversion

Function ConvertDateArray(dateStrings() As String) As Variant
Dim dates() As Date
Dim i As Long
Dim validCount As Long

ReDim dates(LBound(dateStrings) To UBound(dateStrings))
validCount = 0

For i = LBound(dateStrings) To UBound(dateStrings)
On Error Resume Next
dates(i) = DateValue(dateStrings(i))

If Err.Number = 0 Then
validCount = validCount + 1
End If
Err.Clear
Next i

ConvertDateArray = dates
End Function

Date String Normalizer

Function NormalizeDateString(input As String, outputFormat As String) As String
Dim parsedDate As Date

On Error Resume Next
parsedDate = DateValue(input)

If Err.Number = 0 Then
NormalizeDateString = Format(parsedDate, outputFormat)
Else
NormalizeDateString = ""
End If
End Function

' Usage: Convert various formats to ISO
Dim normalized As String
normalized = NormalizeDateString("Jan 15, 2025", "yyyy-mm-dd")  ' Returns "2025-01-15"

Excel Date Converter

Function ExcelDateToVBDate(excelDateStr As String) As Variant
' Excel stores dates as numbers, but when exported may be text
Dim dateVal As Variant

On Error Resume Next

' Try as text date
dateVal = DateValue(excelDateStr)

If Err.Number <> 0 Then
Err.Clear
' Try as Excel serial number
If IsNumeric(excelDateStr) Then
dateVal = CDate(CDbl(excelDateStr))
Else
dateVal = Null
End If
End If

ExcelDateToVBDate = dateVal
End Function

Calendar Date Picker Helper

Function ParseCalendarInput(input As String) As Variant
' Handle various calendar input formats
Dim result As Date

On Error Resume Next

' Remove extra whitespace
input = Trim(input)

' Try direct conversion
result = DateValue(input)
If Err.Number = 0 Then
ParseCalendarInput = result
Exit Function
End If

' Try common substitutions
If LCase(input) = "today" Then
ParseCalendarInput = Date
ElseIf LCase(input) = "yesterday" Then
ParseCalendarInput = Date - 1
ElseIf LCase(input) = "tomorrow" Then
ParseCalendarInput = Date + 1
Else
ParseCalendarInput = Null
End If
End Function

Report Date Filter

Function BuildDateFilter(fromStr As String, toStr As String) As String
Dim fromDate As Date
Dim toDate As Date

On Error Resume Next
fromDate = DateValue(fromStr)
toDate = DateValue(toStr)

If Err.Number = 0 Then
BuildDateFilter = "DateField >= #" & fromDate & "# AND DateField <= #" & toDate & "#"
Else
BuildDateFilter = ""
End If
End Function

Error Handling

Function SafeDateValue(dateStr As String) As Variant
On Error GoTo ErrorHandler

' Validate input
If Len(Trim(dateStr)) = 0 Then
SafeDateValue = Null
Exit Function
End If

SafeDateValue = DateValue(dateStr)
Exit Function

ErrorHandler:
SafeDateValue = Null
End Function

Function SafeDateValueWithDefault(dateStr As String, defaultDate As Date) As Date
On Error Resume Next
SafeDateValueWithDefault = DateValue(dateStr)

If Err.Number <> 0 Then
SafeDateValueWithDefault = defaultDate
End If
End Function

Common Errors

• Error 13 (Type mismatch): String cannot be recognized as a date
• Error 5 (Invalid procedure call): Date is outside valid range

Performance Considerations

• DateValue involves string parsing, slower than DateSerial
• Use DateSerial when constructing dates from numeric components
• Cache parsed dates when processing large datasets
• Locale detection adds overhead
• For known formats, custom parsing may be faster

Best Practices

Always Validate User Input

' Good - Validate before use
On Error Resume Next
userDate = DateValue(txtInput.Text)
If Err.Number <> 0 Then
MsgBox "Please enter a valid date"
Exit Sub
End If

' Avoid - Assuming input is valid
userDate = DateValue(txtInput.Text)  ' May crash

Use IsDate for Pre-validation

If IsDate(txtInput.Text) Then
processDate = DateValue(txtInput.Text)
Else
MsgBox "Invalid date"
End If

Prefer DateSerial for Programmatic Dates

' Good - Fast and unambiguous
dt = DateSerial(2025, 12, 25)

' Less ideal - String parsing overhead
dt = DateValue("12/25/2025")

Be Aware of Locale Issues

' US locale: MM/DD/YYYY
dt = DateValue("3/5/2025")    ' March 5 in US

' UK locale: DD/MM/YYYY
dt = DateValue("3/5/2025")    ' May 3 in UK

' Use unambiguous formats when possible
dt = DateValue("2025-03-05")  ' ISO format, clearer
dt = DateValue("March 5, 2025")  ' Text format, clearer

Comparison with Other Functions

DateValue vs DateSerial

' DateValue - From string representation
dt = DateValue("12/25/2025")

' DateSerial - From numeric components (faster, more reliable)
dt = DateSerial(2025, 12, 25)

DateValue vs CDate

' DateValue - Returns date portion only (strips time)
dt = DateValue("12/25/2025 3:30 PM")  ' Returns 12/25/2025 00:00:00

' CDate - Preserves time information
dt = CDate("12/25/2025 3:30 PM")      ' Returns 12/25/2025 15:30:00

DateValue vs IsDate

' IsDate - Tests if string can be converted (returns Boolean)
If IsDate("12/25/2025") Then...

' DateValue - Actually converts (returns Date or error)
dt = DateValue("12/25/2025")

Limitations

• Locale-dependent interpretation can cause unexpected results
• Cannot directly parse custom date formats
• Limited control over parsing rules
• Strips time information (use CDate to preserve time)
• Two-digit year interpretation fixed (0-29=2000-2029, 30-99=1930-1999)
• Error handling required for user input

Related Functions

• CDate: Converts expression to Date (preserves time)
• DateSerial: Creates date from year, month, day (numeric)
• IsDate: Tests if expression can be converted to date
• Format: Formats date as String (opposite direction)
• TimeValue: Returns time portion from String
• Year, Month, Day: Extract date components
• Date: Returns current system date
• CVDate: Converts expression to Date (legacy function)