In VBA, use CDate to convert a string that VBA recognizes as a date, or DateValue when you want only the date and not a time. Because text such as 03/04/2025 can mean different dates in different regional settings, parse fixed-format input with DateSerial when the format must be unambiguous.
The quick conversion: CDate and DateValue
CDate(expression) converts a valid date or date-and-time expression to VBA’s Date data type. For example, on a system whose regional settings interpret month before day, CDate("March 8, 2025") returns March 8, 2025. The value is a date/time value, not merely a formatted string; how it appears in a worksheet depends on the cell’s number format.
DateValue(expression) is useful when the input may contain a time but the result should represent midnight on that date. For example, DateValue("March 8, 2025 14:30") returns the date March 8, 2025 with a time of 00:00. Use TimeValue for the reverse case when you want the time portion.
Both conversion functions expect text VBA can interpret as a date. If the text is invalid, the conversion raises a run-time error, commonly “Type mismatch.” A basic check is IsDate, but it has the same regional interpretation issue as the conversion itself: it answers whether VBA recognizes the expression, not whether it matches your intended date format.
Why regional settings matter
Date strings are not self-explanatory. The text 03/04/2025 can mean March 4 or April 3, depending on whether the active date convention is month/day/year or day/month/year. VBA’s date parsing follows regional settings, so a macro can behave differently on another computer even when the code and text have not changed.
Named-month text such as 8-Mar-2025 is often easier for a person to read, but it still depends on the language and parsing rules available on the computer. Likewise, an ISO-looking string such as 2025-03-08 should not automatically be treated as a universally safe input for CDate. If a file specification says dates are always year-month-day, explicitly read those components rather than relying on locale-sensitive conversion.
This matters especially when importing CSV files, reading dates from web services, or sharing a workbook across regions. Decide what the source format means, document it, and make the code enforce that format. If users enter dates directly, tell them which order to use or use a date-entry control that removes ambiguity.
Parse a fixed YYYY-MM-DD string with DateSerial
DateSerial(year, month, day) builds a date from numeric components and avoids interpreting a slash-separated date string according to regional conventions. The following function accepts only a four-digit year, two-digit month, and two-digit day in the form YYYY-MM-DD. It returns True only when the components form a real calendar date.
Put this function in a standard VBA module. It checks the input shape, bounds, and the resulting date; the final comparison catches dates that DateSerial would otherwise normalize, such as February 30.
Public Function TryParseISODate(ByVal text As String, ByRef parsedDate As Date) As Boolean
' Accept only the exact YYYY-MM-DD text format.
Dim parts() As String
Dim yearValue As Long
Dim monthValue As Long
Dim dayValue As Long
Dim candidate As Date
text = Trim$(text)
parts = Split(text, "-")
If UBound(parts) <> 2 Then Exit Function
If Len(parts(0)) <> 4 Or Len(parts(1)) <> 2 Or Len(parts(2)) <> 2 Then Exit Function
If Not (parts(0) Like "####") Then Exit Function
If Not (parts(1) Like "##") Then Exit Function
If Not (parts(2) Like "##") Then Exit Function
yearValue = CLng(parts(0))
monthValue = CLng(parts(1))
dayValue = CLng(parts(2))
If yearValue < 100 Or monthValue < 1 Or monthValue > 12 Then Exit Function
If dayValue < 1 Or dayValue > 31 Then Exit Function
candidate = DateSerial(yearValue, monthValue, dayValue)
If Year(candidate) <> yearValue Then Exit Function
If Month(candidate) <> monthValue Then Exit Function
If Day(candidate) <> dayValue Then Exit Function
parsedDate = candidate
TryParseISODate = True
End Function
For example, the function accepts 2024-02-29 and rejects 2025-02-29. The extra checks are important: DateSerial(2025, 2, 29) rolls into March rather than reporting that February 29 is invalid. This behavior can be convenient when deliberately doing date arithmetic, but it is not validation.
Convert text in a worksheet cell
For a simple workbook task, read the source as text, check it, convert it, and write the date value to another cell. The example below reads A2 on Sheet1 and writes the converted date to B2. It uses CDate, so its interpretation still follows the computer’s regional settings; use the strict parser above instead if the source has a guaranteed YYYY-MM-DD format.
Save a copy of the workbook before running a macro. Macros cannot be undone with Ctrl+Z, even if the macro only changes a cell value.
Sub ConvertTextCellToDate()
' Convert the text in A2 and place the date in B2.
Dim sourceCell As Range
Dim sourceText As String
Dim convertedDate As Date
Set sourceCell = ThisWorkbook.Worksheets("Sheet1").Range("A2")
sourceText = CStr(sourceCell.Value2)
If IsDate(sourceText) Then
convertedDate = CDate(sourceText)
sourceCell.Offset(0, 1).Value = convertedDate
sourceCell.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
Else
MsgBox "The value in A2 is not a date VBA can recognize.", vbExclamation
End If
End Sub
The number format changes how the result is displayed; it does not perform the conversion. A real date value can be displayed as 2025-03-08, 8-Mar-2025, or another format without changing the underlying value. If the source cell may contain an Excel error value, handle that condition before calling CStr, because converting an error value to a string can itself raise an error.
Choose the right conversion method
- Use
CDatefor familiar date or date-time text when the input format is known and regional settings are controlled. - Use
DateValuewhen you need only the date portion and can accept the same locale-sensitive parsing. - Use
DateSerialwhen you already have separate numeric year, month, and day values, or when you need to parse a documented fixed format yourself. - Use
IsDateas a preliminary check for ordinary user input, not as proof that a string follows a particular format.
For example, if three fields contain year 2025, month 3, and day 8, building the result as DateSerial(2025, 3, 8) makes the intended order explicit. If those values came from untrusted or imported input, validate their ranges and compare the resulting year, month, and day back to the requested components. Otherwise, an out-of-range day can roll into the following month.
Dates from Excel cells, serial numbers, and display formats
Excel stores dates as serial values with a number format applied for display. A cell that looks like a date may already contain a numeric date value rather than text. In that case, converting its displayed appearance back from text is often unnecessary and can introduce locale problems. VBA’s Range.Value2 returns the underlying value without converting date-formatted cells to the VBA Date subtype; a date-formatted cell will commonly be returned as a number.
When a worksheet cell contains a genuine date value, assign or read the value directly and apply a suitable number format if needed. When it contains text, first establish whether it is truly text and what format the source uses. The distinction is useful when troubleshooting: changing the number format can alter a serial number’s appearance, but it cannot turn arbitrary text into a valid date.
Excel workbooks can also use different date systems, including the 1900 and 1904 systems. That is relevant when transferring raw serial numbers between workbooks: the same number may represent a different calendar date under different systems. Prefer transferring actual date values through Excel’s normal cell operations, and be deliberate when importing or exporting raw serials.
Common errors and safer handling
The most common failure is a mismatch between the assumed format and the computer’s regional settings. Another is accepting a value merely because IsDate returns True; a string may be valid under VBA’s interpretation but not under the data provider’s intended interpretation. Strict parsing avoids that mismatch by checking the exact structure before constructing a date.
- Type mismatch: The text is not recognized, is empty, or is not a date expression. Check the input and handle the failure before using the result.
- Wrong day and month: An ambiguous string was parsed according to a different regional convention. Parse its components based on the source’s stated format.
- Unexpected time:
CDatemay produce a date-and-time value. UseDateValueif the time should be discarded. - Invalid calendar date accepted as another date:
DateSerialnormalizes overflowing components. Validate by comparing the constructed date’s components to the originals. - Text still looks unchanged: A number format affects genuine date values, not text that has not been converted.
For batch imports, decide what should happen to bad rows: skip them, record them in an error column, or stop with a useful message. Avoid silently substituting today’s date or another default, since that can make incorrect records look valid. If timestamps include a time-zone suffix such as Z or an offset, parse and interpret that information separately: VBA’s Date value does not retain a time zone.
Running VBA on Windows and Mac
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On many Mac keyboards, Option+F11 opens it; some keyboards require Fn as well. You can also open the editor from the Developer tab’s Visual Basic command. If the Developer tab is hidden, enable it in Excel’s ribbon settings; the precise settings route can vary between Windows and Mac.
Place a reusable function in a standard module, then call it from another procedure or a worksheet macro. The examples use standard VBA and do not require an add-in. If you run a macro that edits cells, make a backup first: VBA changes are not reversed by Excel’s normal Undo command.
Frequently Asked Questions
How do I convert a string to a date in VBA?
Use CDate(text) when VBA’s regional date parsing matches the input. For a fixed format such as YYYY-MM-DD, split and validate the components, then build the result with DateSerial.
What is the difference between CDate and DateValue?CDate can return a date and time from a date-time expression. DateValue returns the date portion with the time set to midnight. Both parse text according to regional settings.
Does CDate always understand YYYY-MM-DD?
No. Do not assume that a string’s apparent format will be interpreted identically on every computer. For guaranteed year-month-day input, parse the three components and construct the date with DateSerial.
How can I check whether a string is a valid date?IsDate(text) checks whether VBA recognizes the value as a date expression, but its result is locale-sensitive. For a strict input format, validate the text structure and confirm that DateSerial did not normalize invalid components.
Why does DateSerial change an invalid day?DateSerial performs date arithmetic and normalizes values outside a month’s range. For example, an extra day can move into the next month. Compare the returned year, month, and day to the requested parts when validating imported dates.
Related
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find: Search Excel Ranges and Handle Results SafelyIn Excel VBA, Find is a method of a Range, not a separate object: it searches a specified…
- Microsoft Excel Macro Recorder: Record and Run MacrosMicrosoft Excel’s macro recorder captures a sequence of actions in Excel and converts them into VBA code that…
- Copy and Paste a Range in Excel VBA: Methods and ExamplesIn Excel VBA, copy a range with the Range.Copy method and its Destination argument; for example, identify a…
- VBA Range Properties: Values, Formatting, and NavigationIn Excel VBA, a Range property either returns information about cells or another range object, or changes a…
- VBA Loop Format: Syntax, Examples, and When to Use EachA VBA loop repeats a block of code while counting through values, visiting items in a collection, or…
- VBA Send Email From a Shared Mailbox in OutlookYes. In classic Outlook for Windows, VBA can create a message and set its SentOnBehalfOfName property to the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.