MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Text search??


Posted by Rob on August 08, 2001 4:13 PM

I have excel files, each with a "date" column formatted as mm/dd/yy. I'm trying to do a windows file search to find files with todays date, but it never finds any, even when they do exist. It works fine if I search for text, but not dates. Any ideas as to why? Ive tried searching for m/dd/yy, mm/dd/yy, mm/dd/yyyy/, m/d/yyyy, Still nothing. thanks


Posted by Mark W. on August 08, 2001 4:16 PM

What happens when you search for 37111 which
happens to be the date value for 8/8/01?

Posted by Mark W. on August 08, 2001 4:25 PM

Okay, I have your answer...

When "they" say text they mean TEXT!! In Excel,
date values are numeric. Enter
=TEXT(TODAY(),"mm/dd/yyyy") and search for
"08/08/2001" and you'll find the document.

Posted by Rob on August 08, 2001 4:49 PM

Ah yes...that makes sense...

can you say DUH? heh...but the weird thing is, the search for 37111 didn't return anything either...which is werid. oh well..thanks for the help

Posted by Mark W. on August 08, 2001 4:52 PM

Re: Ah yes...that makes sense...

37111 isn't TEXT either. Now, if you'd entered
=TEXT(37111,"0") and then searched for 37111 I
bet you'd find it!!

Posted by Rob on August 08, 2001 5:11 PM

Another Question Mark

OK... the search works if the cell is formatted as TEXT, but...How do I get it put in as text if users manually input the correct date needed. Obviously, they are not going to be expected to put in =TEXT("08/08/01"), so how do I get around this?

I tried one thing:

Date column is C. So say in C1, the user enters todays date as "08/06/01".
I put a formula in D1 of "=TEXT($C1,"mm/dd/yy")", and filled the formula all the way down, as I need it in the whole column. This works, but makes the file 5 mb, which is obviously not reasonable. Is there another way i can get this result? Formatting the row somehow maybe?

I appreciate the help, thanks.

Posted by Rob on August 08, 2001 5:20 PM

ok nevermind :)