How to quickly find missing date from a list in Excel?

Suppose you are maintaining a record or timesheet in Excel, and you notice that your list of dates is not continuous – some dates are missing, as shown in the screenshot. Quickly identifying and filling in these missing dates can help ensure your data is complete for analysis, reporting, or record keeping.
This tutorial introduces several ways to detect and replenish these missing dates efficiently in Excel:
Find missing dates with Conditional Formatting
Find missing dates with formula
Find and fill missing dates with Kutools for Excel
Use VBA to automatically identify and insert missing dates
Highlight missing dates using a Pivot Table

Find missing dates with Conditional Formatting

One approach to pinpoint gaps in your date list is by leveraging Excel's Conditional Formatting. This method visually highlights the cells where a date is missing in a sequence, making it easy to spot discontinuities at a glance.

1. Select the range containing your dates, then go to Home > Conditional Formatting > New Rule. See screenshot:

2. In the New Formatting Rule dialog box, choose Use a formula to determine which cells to format under the Select a Rule Type section. Enter the following formula: =A2<>(A1+1) (where A1 is the first date and A2 is the next date in your list). See screenshot:

3. Click the Format button to open the Format Cells dialog. Under the Fill tab, pick a color to highlight the missing dates. See screenshot:

4. After setting the formatting, click OK twice to apply. Now, cells where there is a missing date in the sequence will be highlighted.

Note: The final date in your list may also be highlighted, as this formula checks for what follows each date. This method is especially useful to quickly review large datasets but does not automatically fill in the missing dates.

Find missing dates with formula

Another practical approach is to use an Excel formula to help identify any gaps directly in your table. This method creates a new column to indicate if a day is missing after each date, making it suitable for tracking attendance logs, project timelines, or inventory records.

In a blank column next to your dates list (say, cell B1 if your list starts at A1), enter the formula: =IF(A2=A1+1,"","Missing next day"). Press Enter, then drag the autofill handle down to copy the formula alongside all dates. See screenshots:

Wherever you see "Missing next day," you know there is a gap in your list. This is an easy, visual way to review missing dates and can be especially helpful if you want to filter or further process the identified gaps.

Note: Like the previous method, the formula will mark the row after the last date (since there's no next date), which you can ignore or clear if not needed.

Find and fill missing dates with Kutools for Excel

For those using Kutools for Excel, there is a built-in feature that can quickly find and even fill missing dates or sequence numbers for you. This is especially helpful when you need not only to locate gaps but also automatically complete your data for accurate calculations or auditing.

After free installing Kutools for Excel, please do as below:

1. Select the date list you want to analyze, then go to Kutools > Insert > Find Missing Sequence Number. See screenshot:

2. In the Find Missing Sequence Number dialog, you can choose among several options, such as finding or inserting missing numbers, highlighting, or creating a marker column. See screenshot:

3. After confirming your choice, click OK. A prompt will display the number of missing dates found. See screenshot:

4. Click OK to complete. Now your list will show, or even fill, the missing dates depending on the option you selected. This approach is convenient for large datasets and minimizes errors from manual checking or formula misplacement.

Insert missing sequence number	Insert blank rows when encountering missing sequence numbers

Insert new column with following missing maker	Fill background color

This tool can save significant time and is especially beneficial for financial statements, attendance data, or any scenario where a continuous date record is required. Make sure your list is sorted by date for best results.

Use VBA to automatically identify and insert missing dates

If you're working with long or frequently updated date lists and want the process to be fully automated, you can use a custom VBA macro in Excel. This method scans your date column, finds missing dates in the sequence, and inserts the missing dates as new rows directly into your list.

This is particularly useful for large datasets, recurring reporting, or when new data is regularly added and you need to ensure completeness without manual checking.

Operation steps:

1. Click Developer > Visual Basic to open the VBA editor. In the pop-up Microsoft Visual Basic for Applications window, click Insert > Module, then paste the following code into the module window:

Sub InsertMissingDates()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentDate As Date, nextDate As Date
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    i = 2
    
    While i < lastRow
        currentDate = ws.Cells(i, 1).Value
        nextDate = ws.Cells(i + 1, 1).Value
        
        If nextDate > currentDate + 1 Then
            ws.Rows(i + 1).Insert Shift:=xlDown
            ws.Cells(i + 1, 1).Value = currentDate + 1
            ws.Cells(i + 1, 1).NumberFormat = "yyyy-mm-dd"
            lastRow = lastRow + 1
        End If
        
        i = i + 1
    Wend
End Sub

2. Click the Run button (or press F5) to execute the code. The macro will check your first column (Column A) for a list of dates and automatically insert missing dates as new rows.

Practical tips and notes:
– Ensure your dates are sorted in ascending order before running the macro.
– The macro inserts missing dates as new rows, so backup your data or test on a copy if needed.
– If your dates are not in Column A, tweak ws.Cells(i,1) to the correct column number.
– In case of a very large dataset, the macro may take a few moments to complete.
– If you receive an error, verify that all cells in your date column are actual date values.

Highlight missing dates using a Pivot Table

If you prefer not to use formulas or code, you can leverage Excel’s built-in Pivot Table feature to visually compare your actual list of dates to a full expected sequence. This method is particularly suitable when you want to analyze or cross-check attendance logs, transactions, or daily records where every date in a range should appear.

Operation steps:

1. First, create a helper column that contains a full sequence of expected dates covering your start and end dates. Enter the first date in a cell (e.g., D2), then drag the fill handle down to create dates until your range is complete.
2. Copy both your original dates list and your new helper date list into a new worksheet, stacking them in one column (for example, column E).
3. Select the combined list, then go to Insert > PivotTable. In the dialog, set the table/range and choose a new worksheet for the output.
4. In the Pivot Table Field List, drag the date field to the Rows area and again to the Values area, setting the aggregation to Count. Dates with only one appearance in the count column indicate missing dates (i.e., those only present in the full sequence but not your actual data).

Tips:
– This method works best for reviewing missing entries across large periods.
– For optimal results, ensure your date lists do not contain duplicates.
– You can filter or highlight in the Pivot Table to quickly spot missing dates.
– Advanced users can combine this method with Conditional Formatting for additional visibility.

Pros: Easy to visualize; no formulas or VBA required; ideal for reporting.
Cons: Does not automatically fill missing dates but highlights what’s missing.

Demo: Find and insert missing date in list