Searching for Dates

Written by Allen Wyatt (last updated July 13, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


1

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Multiple Print Areas on a Single Printed Page

Want to print small, non-contiguous areas of your worksheet all on a single page? You might think that defining a ...

Discover More

Hiding Errors on Printouts

If there are error values in a worksheet, you may not want those error values to appear on a printout. Excel actually ...

Discover More

Getting the Traditional Open Dialog Box

When you attempt to open a file in Word, chances are good that you will first have to pass through what is called the ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Replacing Tildes at the Beginning of a Cell

Replacing a specific character (such as a tilde) seems a simple task, until you need to replace it only in a certain ...

Discover More

Finding Boolean Values

Excel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or ...

Discover More

Using Find and Replace to Find Conditionally Formatted Cells

You can use Find and Replace as a quick way to count any number of matches in your document. You cannot, however, use it ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 3?

2024-07-13 13:01:51

Ron S

I had the impression that you wanted to search across multiple tabs. To the best of my knowledge Find only works on a single sheet.

I'm assuming the sheets in the workbook all have the same structure, separating geographic regions or other simple specific data

At this point I would consider changing the structure of the workbook. Move all of the sheets into a single sheet, adding a column(s) to save the sheet names/type data. Then you can use the Find suggestions Allen provided

If you can't get rid of the separate tabs you could use PowerQuery to bring them together into a single "Find" or "Master" tab. On the master tab you can a slicer to recreate the filter of the original tab. Or, you could use a Pivot Table to recreate the original tabs.

Note: you can do the merge "manually", or use PowerQuery to automate the process.

Convert to the Excel Table format. It is a trivially simple change that enables several new functions You can use it to generate a pivot table to recreate the data that was on separate sheets.

On an Excel Table and/or Pivot Table you can add "Slicers", or "Timeline Slicers" to do the equivalent of a "find" by date or date ranges.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.