Searching for Dates

Simon has a worksheet that includes date columns. He wants to search for a specific date, say 25 January 2021, formatted as 25/01/21. Simon doesn't know how to set up the Find and Replace dialog box to find it or even if it is possible.

Searching for dates is not as simple as Excel users think it should be. I'm one of those users; it should be easier. Period. But, it is not. Let me show you what I mean.

Let's take Simon's example—in a cell he has the date 25/01/21. This date is in the format d/mm/yy, but that doesn't mean that is what the cell is formatted as. If Simon is using a version of Windows where the regional settings use the d/m/y format, then Simon's date isn't truly formatted because when it was entered, Excel parsed the entry as a date and simply displayed it using the Short Date setting specified in Windows.

However, if Simon's system uses regional settings that rely on a m/d/y format, then the only way Simon could have the date displayed as 25/01/21 is to apply a custom format of d/mm/yy or dd/mm/yy to the cell in Excel, thereby overriding the default date display format.

Now, let's look at how all of this affects searching for things in Excel. The Find and Replace dialog box has three pertinent settings:

• What to search for
• What format to search for
• A "look in" setting

This obviously leads to multiple permutations of how to search when it comes to dates, and the permutations can be confusing.

Testing has shown, though, that the "look in" setting (Formula or Value) doesn't really matter in Simon's case, except in one instance which I'll get to shortly. Further, the Format setting only matters if Simon's data uses multiple differing date formats and he wants to find a date displayed using a specific format. (In that case, the format specified in the Find and Replace dialog box would need to match the exact format that Simon wants to find.)

That leaves the "what to search for" field. This typically needs to be set to exactly what is displayed in the cell that Simon wants to locate. In other words, if he searches for 25/01/21, then Excel will easily find the cell about which he first asked. However, if Simon searches for 25/01/2021, 25/1/21, or 25/1/2021, then the date 25/01/21 will not be matched; it won't be found. Further, if Simon searches for the date/time serial number that Excel uses internally—which would be 44221 for the date 25/01/21—it also won't result in the date being found.

Finally, notice that I said a moment ago that what you search for "typically needs to be set to exactly what is displayed." There is one case where this may not be true. Let's say that Simon's system uses a regional setting that displays dates in the m/d/y format and that, within Excel, Simon has applied a custom format of d/mm/yy to the cell. If Simon searches for 25/01/21, then his date will be found because the display matches what he is searching for. Plus, if he searches for 1/25/21 and the "look in" setting is set to Formulas, then Excel will also find the cell because that is how the date appears in the Formula bar—it is how Excel considers the date to "really be," which matches the regional settings.

To complicate matters just a bit more (is that possible?), if the date that Simon wants to find is in a row hidden by a filter, then Excel won't find it. However, if the date is in a row that has been hidden manually, then it will find the date.

This is fun, huh? It's why I mentioned at the beginning of this tip that searching for dates is nowhere as simple as it should be. The best rule of thumb is to search for the date exactly as it is displayed, using a "look in" setting of Values and no special format.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13930) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.