Search for dates in excel

Question

Hello,

I have dates in an excel field of these formats, 01-jan-10, 02/02/05. I formated the cells to 03-mar-2010.

I did a find such as 03-mar-2010 does not turn up any result. Any reason to why.

Thanks Jean

Answer 1

The cells with the data information must be formatted as cells, from the format->cells menu. Possibly your dates are typed the way you want them, but are in text or general format.

UPDATE: I just checked in Excel 2003, and running a Find using 03-mar-2010 won't return anything. You have to use something like 3/3/2010.

Answer 2

Don't forget that Excel stores dates internally in a numeric format (just do Edit>Clear>Formats to prove it) so you'll need to do something a little different.

Here's how to find dates formatted in a particular way: (I've tested these in Excel 2003, should be similar in 2007)

In the Edit, Find box, do the following:

1. Click the Options >> button
2. Under Look in:, change the drop-down to Values
3. On the right-side, click the Format... button
4. Choose the same date style you are using in your spreadsheet
5. In the Find what: box, type the date you are searching for, in the same format
6. Click Find Next

Answer 3

CTRL+F -> click options -> change the drop down menu to look in values