Excel VBA: Find the Latest Date in a Column Automatically

To find the latest date in an Excel column with VBA, scan the cells that contain dates, keep the greatest value, and report it. The macro below checks column A from row 2 down, skips blanks and non-date entries, and displays the latest date it finds; adjust the worksheet name and column to match your data.

How Excel dates make this work

Excel stores ordinary dates as serial numbers: each date corresponds to a number, and later dates have larger numbers. The cell’s number format makes that serial appear as a familiar date, such as 2-Nov-2025. Therefore, finding the latest date is usually a maximum-value problem, not a matter of comparing the text you see on screen.

The macro uses VBA’s IsDate function to check whether a cell value can be treated as a date, then compares the converted values with CDate. It does not assume that the bottom-most filled cell contains the latest date. For example, if November appears above an older September entry, it still finds November.

This approach assumes the selected column is intended to hold dates, perhaps with blanks, a heading, or occasional text such as “Pending.” Avoid mixing unrelated numbers into the date column: numeric values and Excel date serials can be difficult to distinguish reliably. If dates are stored as text, VBA’s interpretation can also depend on the computer’s regional date settings.

VBA macro for the latest date in column A

In this example, the worksheet is named Sheet1, row 1 contains a heading, and dates begin in A2. The code checks through the last non-empty cell in column A and shows the latest date, including a time if one is present.

Sub FindLatestDateInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range
    Dim latestDate As Date
    Dim foundDate As Boolean

    ' Set the worksheet that contains the dates.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Find the last non-empty cell in column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Check the data rows, leaving the heading in row 1 alone.
    For Each cell In ws.Range("A2:A" & lastRow)
        If Not IsError(cell.Value) Then
            If IsDate(cell.Value) Then
                If Not foundDate Or CDate(cell.Value) > latestDate Then
                    latestDate = CDate(cell.Value)
                    foundDate = True
                End If
            End If
        End If
    Next cell

    If foundDate Then
        MsgBox "Latest date: " & Format$(latestDate, "dd-mmm-yyyy hh:mm")
    Else
        MsgBox "No dates were found in column A."
    End If
End Sub

The first valid date becomes the initial maximum. Each later date replaces it only if it is greater. The foundDate flag matters because a variable declared as Date starts with a default value; without the flag, that default could be mistaken for a date found in the worksheet. The error check prevents an Excel error value from interrupting the scan.

How to add and run the macro

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z.
In desktop Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. You can also choose Developer > Visual Basic. If the Developer tab is not visible, enable it in File > Options > Customize Ribbon.
In the Visual Basic Editor, choose Insert > Module, then paste the code into the module.
Change Sheet1 to the exact worksheet tab name that holds your dates. Keep the name inside the quotation marks.
Run the procedure with the cursor inside it by pressing F5, or return to Excel, choose Developer > Macros, select FindLatestDateInColumnA, and click Run.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code. Excel for the web can open a macro-enabled workbook, but it cannot run VBA macros; use desktop Excel to execute this code. Excel for Mac supports VBA in the desktop app, though keyboard shortcuts can vary by keyboard. The Visual Basic Editor is available from the Developer tab; on many Mac keyboards Option+F11 opens it, and the Fn key may also be required.

Change the worksheet, column, and starting row

For dates in column D on a sheet named “Orders,” change the worksheet assignment to Set ws = ThisWorkbook.Worksheets("Orders"). Then replace each reference to column A with D: use ws.Cells(ws.Rows.Count, "D").End(xlUp).Row to find the final row and ws.Range("D2:D" & lastRow) for the scan. The sheet name must match the tab name, including spaces.

If your data has no heading and begins in row 1, change the scan range from row 2 to row 1. If it begins in row 5, scan from row 5 instead. Keep the row used to find the last entry consistent with the column being checked. The End(xlUp) method moves upward from the bottom of the worksheet to the last non-empty cell in that column; it does not find the last date specifically.

If the column may be entirely empty, the current range construction still refers to A2:A1 when the last used row is 1. That is a small range and the loop will not identify a date, but for clearer handling you can add an early check after finding lastRow: if it is less than 2, show the “No dates” message and exit the procedure. This is especially useful when adapting the macro to a sheet with no heading or a different start row.

Get the latest date in a cell instead of a message

A message box is convenient for a quick check, but a worksheet result is easier to reuse. To write the result into B1, replace the message-box section after the loop with an output block that puts latestDate in B1 when a date was found and otherwise writes a suitable message. Format B1 as a date or date-and-time so Excel displays the value clearly. The stored value remains a date that can be used in other formulas.

For example, if the scan finds 2-Nov-2025 at 3:30 p.m., the macro’s display format shows 02-Nov-2025 15:30. If your source entries contain times but you care only about the calendar day, use a date-only display format such as dd-mmm-yyyy. That changes what is displayed, not the underlying value. To compare calendar days while ignoring times, compare the date-only portions rather than relying on formatting alone.

Formula alternative for a date-only result

If you do not need a macro, a worksheet formula can be simpler. With genuine Excel dates or blanks in A2:A100, enter =MAX(A2:A100) in another cell. For example, if the range contains the actual dates 12-Mar-2025, 31-Dec-2024, and 2-Nov-2025, along with blanks, the result is 2-Nov-2025. Format the formula cell as a date if Excel initially displays a serial number. MAX ignores text in a referenced range, but an error value in the range can cause an error result.

The formula recalculates when the data changes, while the macro runs only when you run it. For a changing list, use a larger range or an Excel Table column reference so new entries are included. A formula is often best for a live result; VBA makes sense when the result is part of a larger automated task, such as checking several sheets or writing a report. Neither method can reliably infer that an arbitrary number is meant to be a date if the column also contains other numeric data.

Common problems and how to fix them

The macro says no dates were found. Confirm that the worksheet name and column are right, and that the data starts on the row used in the range. Check whether the entries are real Excel dates or text that VBA does not recognize under the current regional settings.
The result is not the date that looks latest. A date stored as text may not compare like a real date. Convert the entries to Excel dates, and check that the cells do not contain unexpected times. A value late in the day can be greater than another value with the same calendar date.
The code stops with a subscript error. Usually the worksheet name does not match a tab in the workbook containing the macro. If the code is stored in a different workbook, ThisWorkbook refers to the workbook containing the code, not necessarily the workbook currently selected.
The output shows a number rather than a date. The result may still be a valid Excel date serial. Apply a date number format to the output cell, or use the macro’s formatted message display.
The scan misses data below a gap. End(xlUp) finds the last non-empty cell in the column, including entries below blank rows, so a gap by itself is not a problem. It will not include dates in another column or on another worksheet.

Frequently Asked Questions

How do I find the latest date in Excel VBA?

Loop through the date cells, test each value with IsDate, and keep the greatest value found. The example macro scans column A and displays the maximum recognized date.

Can I find the latest date without VBA?

Yes. With dates in A2:A100, enter =MAX(A2:A100) in a cell and format the result as a date. Use VBA when you need to automate additional steps or produce a message or report.

Does the macro ignore blank cells and text?

Yes. Blank cells and values that IsDate does not recognize are skipped. Text that looks like a date may be accepted, but its interpretation can depend on regional date settings.

How do I find the latest date on another worksheet?

Change the worksheet assignment to the exact tab name, for example Set ws = ThisWorkbook.Worksheets("Orders"). Also change the column references and starting row if the dates are not in A2 downward.

Why does the result include a time?

Excel date-time values include a time fraction, and the macro compares the full value. The example displays hours and minutes. Use a date-only format if you want to hide the time, or adjust the comparison if times should not affect which calendar date is considered latest.

Choosing the right approach

Use MAX when you want a visible result that updates as entries change. Use VBA when finding the date is one step in a repeatable process, or when the result must be placed or reported automatically. Before relying on either method, make sure the source cells contain consistent Excel dates, decide whether times matter, and test the result against a few known entries. Those checks prevent the most common mistake: treating a value that merely looks like a date as though Excel necessarily stores it as one.

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.