To match a date in an Excel range with VBA, use Application.Match with match type 0, which requests an exact match. For example, with dates in A2:A100, Application.Match(CDbl(DateSerial(2025, 3, 8)), Range("A2:A100"), 0) returns the matching item’s position in that range, or an error value if it is not found.
How Excel Stores Dates
Excel usually stores a date as a number, not as a special text string. The whole-number part represents the day, and any decimal fraction represents the time. For instance, a cell formatted to display only a date may still contain a time such as 3:00 PM. Its appearance can therefore look like a match even when its underlying value differs from a date entered at midnight.
Date formatting controls how a value appears; it does not change the stored value. A cell can display as 8-Mar-2025, while another displays as 3/8/2025, and both can contain the same date serial. Conversely, text that looks like a date is not necessarily the same value as an actual Excel date. This distinction explains many failed lookups.
For ordinary modern dates in a workbook using Excel’s default 1900 date system, converting a VBA Date to a number with CDbl gives a value that can be compared with worksheet date serials. Excel workbooks can also use the 1904 date system, which shifts worksheet serial values. If the workbook uses that option, test date conversions carefully instead of assuming a raw VBA serial will match; the setting is under File > Options > Advanced, in the When calculating this workbook section.
Use Application.Match for an Exact Date
The third argument to Match is important. Use 0 for an exact match; this works whether the lookup range is sorted or unsorted. The function returns a relative position, not the worksheet row number. If the range starts at A2 and the result is 1, the match is in A2; a result of 3 means A4.
The example below searches A2:A100 on a sheet named Data, then reports the matching cell address. It uses DateSerial to construct the target date without relying on how Windows interprets a typed date such as 3/8/2025.
Sub FindExactDate()
Dim ws As Worksheet
Dim dateRange As Range
Dim targetDate As Date
Dim matchResult As Variant
Dim foundCell As Range
' Build an unambiguous date: year, month, day.
Set ws = ThisWorkbook.Worksheets("Data")
Set dateRange = ws.Range("A2:A100")
targetDate = DateSerial(2025, 3, 8)
' Match type 0 searches for an exact value.
matchResult = Application.Match(CDbl(targetDate), dateRange, 0)
If IsError(matchResult) Then
MsgBox "Date not found."
Else
Set foundCell = dateRange.Cells(CLng(matchResult), 1)
MsgBox "Found in " & foundCell.Address(False, False)
End If
End Sub
Before running a macro, save a copy of the workbook. Macro actions generally cannot be undone with Ctrl+Z, even if a particular example only displays a message. On Windows, open the VBA editor with Alt+F11; on Mac, the common shortcut is Option+F11, though some keyboards require Fn as well. Macros must be run in the desktop Excel application, not Excel for the web.
Account for Times in Date Cells
An exact match compares the complete stored value, including the time fraction. If A2 contains 8-Mar-2025 at 9:00 AM and the target is 8-Mar-2025 at midnight, an exact Match will not find it. Changing the cell’s number format to show only the date does not remove the time.
If the goal is to find the first cell on a calendar day regardless of time, loop through the range and compare each valid date after removing its time component. This approach also makes it straightforward to identify the actual worksheet cell rather than translating a position later.
Sub FindDateIgnoringTime()
Dim ws As Worksheet
Dim dateRange As Range
Dim targetDate As Date
Dim cell As Range
Dim foundCell As Range
Set ws = ThisWorkbook.Worksheets("Data")
Set dateRange = ws.Range("A2:A100")
targetDate = DateSerial(2025, 3, 8)
For Each cell In dateRange.Cells
If Not IsError(cell.Value) Then
If IsDate(cell.Value) Then
' Compare calendar dates, ignoring the time fraction.
If DateValue(CDate(cell.Value)) = targetDate Then
Set foundCell = cell
Exit For
End If
End If
End If
Next cell
If foundCell Is Nothing Then
MsgBox "Date not found."
Else
MsgBox "First date found in " & foundCell.Address(False, False)
End If
End Sub
This loop returns the first matching date in range order. If you need every row for that day, remove Exit For and collect or process each matching cell inside the loop. If the data is large, a helper column containing date-only values or an array-based approach may be more efficient than checking cells one at a time.
Dates Stored as Text
Text dates are a separate problem from dates containing times. A value such as the text “08/03/2025” might mean August 3 or March 8 depending on regional conventions, and it will not necessarily match a numeric Excel date serial. A cell’s left alignment can hint that a value is text, but alignment is customizable and is not a dependable test by itself.
For dependable data, convert text dates to real dates before searching, and verify ambiguous entries against the source. DateSerial(year, month, day) is a clear way to construct known dates in VBA. DateValue can convert date text, but its interpretation can depend on regional settings. Avoid converting uncertain text silently: a macro could interpret a day and month in the wrong order while still producing a valid date.
For a quick check in a worksheet, select a suspected cell and inspect the formula bar and number format. A numeric date can be formatted temporarily as General to reveal its serial value. A text date remains text. Do not rely on the displayed format alone to decide which kind of value the cell contains.
Handle Missing Dates and Duplicate Matches
Application.Match returns an error value when there is no match. Storing the result in a Variant and checking IsError, as in the example, avoids treating a missing match as a valid row number. By contrast, WorksheetFunction.Match raises a VBA run-time error when no match exists unless the code handles that error. For routine lookups where absence is expected, the Application form is often simpler.
If the same date appears several times, Match returns the position of the first matching item. It does not return all matching rows. For example, if A5 and A12 both contain the target date and the searched range starts at A2, an exact Match returns position 4, corresponding to A5. Use a loop when you need to process duplicates or choose a match based on another condition, such as a customer ID or status.
Also confirm that the lookup range contains the expected cells and is on the intended worksheet. A relative position of 4 is meaningful only in relation to the exact range passed to Match. If you need the worksheet row number after a successful lookup, use the matched cell’s Row property, or add the relative position to the range’s starting row with care.
Choose Between Match, Find, and a Loop
- Application.Match: A concise choice for finding one exact numeric date in a range. Check the returned value with
IsError; remember that it gives a relative position and returns only the first duplicate. - Range.Find: Useful when you want a Range object and its address. Its search settings can persist from earlier Excel searches, so specify relevant arguments such as
LookIn,LookAt, andSearchDirectionrather than relying on prior settings. Date display formats can also make Find behavior confusing, so verify what is actually stored. - A VBA loop: Best when matching by calendar day while ignoring time, processing every duplicate, or applying extra conditions. It is easy to read but can be slower for very large ranges if each cell is accessed individually.
For a straightforward exact date lookup, Match is usually the smallest solution. For a more complicated rule, making the comparison explicit in a loop is often easier to maintain than trying to adapt a simple lookup to conditions it was not designed to express.
Common Errors and Practical Checks
If a lookup unexpectedly returns no match, check these points before rewriting the macro:
- Confirm that the target and worksheet value have the same time component, or use the date-only loop.
- Check whether the worksheet entry is a real date serial or text that merely looks like a date.
- Use
DateSerial(2025, 3, 8)rather than an ambiguous typed date in VBA code. - Verify that the range includes the cell and that the worksheet name is spelled correctly.
- Use match type
0; approximate matching is inappropriate for an exact date search. - Check whether the workbook uses the 1904 date system if values appear consistently offset.
Another frequent misconception is that the cell’s format controls matching. Formatting only changes presentation. Similarly, a match that returns 1 does not mean the date is in row 1; it means it is the first item in the supplied range. These two distinctions resolve many issues that otherwise look like VBA bugs.
Frequently Asked Questions
How do I find a date in Excel VBA?
Use Application.Match(CDbl(targetDate), dateRange, 0) for an exact match against real date values, then check the Variant result with IsError. The returned number is the position within the searched range.
Why does VBA Match not find a date that looks identical?
The cell may contain a time fraction, text rather than a numeric date, or a value from a workbook using a different date system. Cell formatting can hide these differences. Inspect the stored value and decide whether your comparison should include time.
How can I match a date while ignoring time?
Loop through the cells and compare DateValue(CDate(cell.Value)) with a target date created using DateSerial. That compares calendar days and returns the first matching cell in the example; remove the early exit if you need all matches.
Does Match return a row number?
No. It returns a position relative to the lookup range. If the range is A2:A100 and Match returns 5, the match is in A6. Use the range’s cell at that relative position to get its address or worksheet row.
Can I find every row with the same date?
Not with a single Match call, which returns only the first match. Use a loop and process every qualifying cell, or use a worksheet helper column if you want to filter or review all matching records.
Will the macro run in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use desktop Excel for Windows or Mac to run the code.
Related
- Excel Split Contents of a Cell Into Two Cells
- Excel Split Multiline Cell Into Rows
- Excel Split Empty Cells Into Multiple Rows?
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Keyboard Shortcuts: Assign and Run MacrosIn Excel for Microsoft 365 on Windows, assign a macro shortcut by choosing Developer > Macros, selecting the…
- Excel VBA: How to Cancel a LoopTo cancel a running Excel VBA loop on Windows, press Ctrl+Break; Esc may also work, especially when the…
- Excel VBA Data Entry Forms: Setup, Code, and TipsAn Excel VBA data entry form is a custom window for collecting information and writing it to a…
- VBA Sort Range by Column: Practical Examples and TipsTo sort a range by one column in Excel VBA, call the range’s Sort method and specify the…
- Excel VBA Find a Value in an Array: Loop, Match, and TipsTo find a value in a VBA array, loop from LBound to UBound and compare each element with…
- Excel VBA: How to Check Whether Cell Colors Match ExactlyYes. In Excel VBA, compare the cells’ Interior.Color values; they are numeric color values, so an equality test…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.