How to quickly find missing date from a list in Excel?
Find missing dates with Conditional Formatting
Find missing dates with formula
Find and fill missing dates with Kutools for Excel
To find the missing dates from a list, you can apply Conditional Formatting function in Excel.
1. Select the date list, and then click Home > Conditional Formatting > New Rule. See screenshot:
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format in the Select a Rule Type section, and then type this formula =A2<>(A1+1) (A1 is the first cell in the date list, and A2 is the second cell) into the Format values where this formula is true text box. See screenshot:
3. Then click Format button to go to the Format Cells dialog, and the under Fill tab, select a background color to highlight the missing dates as you need. See screenshot:
4. Click OK > OK, then the position of the missing dates are highlighted.
Note: The last date in the date list will be highlighted.
Here you also can use a formula to quickly find the missing dates from the list.
Select a blank cell which next to the first cell of the date list, for instance, B1, and type this formula =IF(A2=A1+1,"","Missing next day"), then press Enter key and drag the autofill handle down to over the cells which need this formula. See screenshot:
Note: This formula will also add the “Missing next day” besides the last date in the date list.
If you have Kutools for Excel – an Excel handy tool installed, you can quickly find and fill the missing dates or sequence number with its Find Missing Sequence Number utility.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
1. Select the date list you want to find the missing dates, and click Kutools > Insert > Find Missing Sequence Number. See screenshot:
2. In the Find Missing Sequence Number dialog, you can select the operation as you need. See screenshot:
3. And the Ok, and a dialog will pop out to tell you the number of missing sequence dates. See screenshot:
4. Click OK, now you can see the missing dates are found or filled.
|Insert missing sequence number||Insert blank rows when encountering missing sequence numbers|
|;Insert new column with following missing maker||Fill background color|