Excel VBA: Find a Date in a Range

In Excel VBA, use the Range.Find method to locate an exact date in a range; set its search options explicitly because Excel remembers several Find settings. If cells may include times and you want to match the calendar day regardless of time, loop through their numeric date serials and compare the integer parts instead.

How Excel stores dates

Excel stores a real date as a number, not as a special text string. The whole-number part represents the day, and any fractional part represents the time. For example, a cell containing November 2, 2026 at noon has a different underlying value from a cell containing midnight on the same day, even if both cells are formatted to show only the date.

This distinction matters when choosing a search method. A search for one exact date value may not match a cell that contains that date plus a time. The displayed format can also differ from the stored value: the same date may appear as 11/2/2026, 2-Nov-2026, or another regional format.

The examples below assume that the search range contains genuine Excel dates, rather than date-looking text. They use Microsoft Excel for Microsoft 365 on Windows, and the same VBA logic generally applies to Excel for Mac. Excel for the web can open a workbook containing macros, but it cannot run or edit VBA macros.

Find an exact date with Range.Find

Use Find when you want a cell whose stored value matches the target date and you want the first matching cell. In this example, the dates are in A2:A100 on a worksheet named Sheet1. If A5 contains the target date as a real Excel date, the macro returns A5’s address in its message.

Sub FindExactDate()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim targetDate As Date

    ' Use an unambiguous date instead of a regional date literal.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")
    targetDate = DateSerial(2026, 11, 2)

    ' Set every important Find option; some settings persist from Excel's dialog.
    Set foundCell = searchRange.Find( _
        What:=CDbl(targetDate), _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Date not found."
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
End Sub

DateSerial(2026, 11, 2) constructs November 2, 2026 without relying on how Windows or Excel interprets a typed date. Passing its numeric value with CDbl makes the target’s date serial explicit. LookAt:=xlWhole requests a whole-value match rather than a partial match, and LookIn:=xlValues searches calculated cell values rather than formula text.

After must be one cell in the search range. Here it is set to the last cell, so the search proceeds from the beginning and can wrap around. Find returns a Range object when it finds a match and Nothing when it does not. Always check for Nothing before asking for the result’s address or value.

Match a date even when cells contain times

If the range may contain timestamps, compare the whole-number parts of the stored serial values. This treats midnight and later times on the same calendar day as a match. The following version returns the first matching cell in the same A2:A100 range. It assumes the column holds actual Excel dates or date-times, not unrelated numbers.

Sub FindDateIgnoringTime()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range
    Dim targetDate As Date
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")
    targetDate = DateSerial(2026, 11, 2)

    For Each cell In searchRange.Cells
        cellValue = cell.Value2

        ' Real Excel dates are numeric serials; ignore text and other values.
        If IsNumeric(cellValue) Then
            If Fix(CDbl(cellValue)) = Fix(CDbl(targetDate)) Then
                MsgBox "Found at " & cell.Address(False, False)
                Exit Sub
            End If
        End If
    Next cell

    MsgBox "Date not found."
End Sub

Value2 exposes the underlying numeric value without converting it to VBA’s Date subtype. Fix removes the fractional time portion for ordinary positive Excel date serials. The loop checks cells in range order, so it stops at the first match. If you need to act on every matching row, remove the message and Exit Sub from the loop, and put the row-processing action there instead.

Find every occurrence

A range can contain the same date more than once. For a straightforward scan, process each match inside the loop shown above and let the loop continue. For example, a macro could collect the matching row numbers, copy those rows, or count matches. Avoid displaying a message for every hit in a large range; write results to a worksheet or show one summary after the scan.

You can also use FindNext after a successful Find, but the search eventually wraps around to its first result. Save the first result’s address and stop when that address appears again. Without that stopping condition, a loop can run indefinitely. For date-only matching where cells may contain times, the explicit cell loop is usually easier to understand and control.

Choose the right range and worksheet

A search is only as reliable as the range it examines. A fixed address such as A2:A100 is easy to read, but it will miss new records placed below row 100. If your data is an Excel Table, referencing its date column is often more maintainable because the table expands as rows are added. A named range can also make the intended search area clear.

Qualify the worksheet and workbook rather than relying on whichever sheet happens to be active. In the examples, ThisWorkbook means the workbook containing the macro. If the code is stored in a personal macro workbook but should search a different open workbook, use an explicit workbook reference instead. A misspelled sheet name causes a runtime error, so confirm the worksheet tab name matches the code.

For a range spanning more than one column, Find searches according to the specified order. If you use a loop, decide whether to scan every cell or just the date column. Searching a focused range reduces work and avoids accidentally treating a number elsewhere in the sheet as a date.

Common problems and how to fix them

The date looks right but is not found: Check whether the cell contains a real date or text. A value pasted as text can look identical to a date while having no numeric date serial. Convert the source data to real dates before relying on numeric comparisons.
The cell includes a time: An exact comparison can miss it because the fractional part differs. Use the date-only loop with Fix when the time should not matter.
Find behaves differently after using Ctrl+F: Excel retains some Find dialog options. Supplying LookIn, LookAt, search direction, order, and format options in VBA prevents old dialog choices from silently changing the search.
The macro finds an unexpected number: The date-only loop treats every numeric value in the selected range as a possible serial. Keep the range limited to the date column; do not use it on a mixed column of dates, IDs, and amounts.
The target changes by region: Avoid ambiguous VBA date literals such as #11/2/2026#. Use DateSerial(year, month, day) so the argument order is explicit.
A formula-produced date is missing: The Find example uses LookIn:=xlValues, which searches the formula result. If you select formulas instead, you search formula text rather than the displayed result.

One important distinction is that formatting a cell as a date does not convert arbitrary text into a genuine date value. If dates arrive from a CSV or another system, inspect a few cells and correct the data type first. Conversely, do not convert values blindly if the column contains codes or numbers that only happen to resemble date serials.

Find versus a VBA loop

Range.Find is compact and convenient for an exact match in a clean date column. It returns the matching cell directly and is a natural choice when an exact stored value is what matters. Its main disadvantages are the persistent search options and the extra care required when searching repeatedly with FindNext.

A loop takes more lines but makes the comparison rule visible. It can ignore times, skip nonnumeric cells, and apply additional conditions such as checking another column on the same row. For a small or moderate date range, this clarity is often worth more than shortening the code. For very large ranges, performance depends on the amount of data and work done per cell; test with the actual workbook before optimizing.

Neither approach can reliably infer what a text string means in every regional format. If a source contains text such as a date written with slashes, first decide which day-month order the source uses and convert it deliberately. Treating ambiguous text as a date without knowing its convention can produce a technically successful search for the wrong day.

Run the macro safely

Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA. Before running a macro on important data, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even when the macro’s result appears simple. The examples only display a message and do not change worksheet cells, but a modified version may not be so harmless.

On Windows, macros may be blocked by Trust Center settings or by file security controls. Enable macros only for workbooks you trust; do not lower security settings just to run unknown code. Excel for Mac supports VBA, though the Visual Basic Editor and security prompts may look different. Excel for the web does not execute VBA, so run the macro in the desktop application.

Frequently Asked Questions

How do I find a date in a specific Excel column with VBA?
Set a Range variable to that column’s date cells, then call Find with the target date and explicit options. Check whether the returned Range is Nothing before using it.

Why does VBA not find a date when the cell shows the same date?
The cell may contain a time, a text string, or a different underlying serial value. Compare numeric serials, use a date-only comparison if times are irrelevant, and verify that the cell is a real Excel date.

How can I find a date and ignore the time?
Loop through the date cells and compare Fix(CDbl(cell.Value2)) with Fix(CDbl(targetDate)), after confirming the cell contains a numeric value. This compares the calendar day while disregarding the time fraction.

Can VBA find all cells with the same date?
Yes. Scan the range and process each match without exiting after the first one. If using FindNext, stop when the search returns to the first matched address so the wraparound does not repeat results forever.

Can I run this VBA in Excel for the web?
No. Excel for the web can preserve a workbook that contains VBA, but macros must be run in desktop Excel. Excel for Windows and Excel for Mac support VBA, although their editor interfaces and security prompts differ.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.