Matching Dates

[thejayjetson]

Help me tell Excel to recognize a date as a date, and match with another date.

Details
I have a huge list of dates that are seen as text.
Example of their format: Dec 15, 2011 at 12:00 AM

The first formula I tried was to use
=LEFT(A1,SEARCH(" at ",A1)-1)

That works fine for displaying just the date without the time.
But the date is still recognized as text.
Then I tried:
=TEXT(A1,"mmmm dd, YYYY")
&
=DATEVALUE(A1)
Those both help me displayed the date in a different way,
but still the date is not recognized as a date.

When I go to match the same date in another cell,
I get FALSE.
But of course in another cell that I manually type the date in,
and attempt matching, I get TRUE.

I need to see that TRUE without having to retype every cell.
I as a human see the dates are the same, and I need to tell Excel how to do this,
so it can do the work I need it to do.

[chrisb84]

thejayjetson,

See if this works for you (I'm using Excel 2010):

1. copy and paste your column of date/time info to a new column. (this keeps your original data "pure")
2. Highlight the date/time info you want to convert (I assume all of it) and use the Text-To-Columns feature on the Data Tab.
3. Select the "Fixed width" radio button and click "next"
4. DELETE all of the break lines EXCEPT the one between the year and "at", click "next"
5. Highlight the "date" portion in the Data Preview section and select "Date", MDY in the Column data format. You can leave the "at xx:xx am/pm" as General Format (this assumes that the time data is unimportant to you.)
6. Click "Finish"

You should now have a column of date-data that is recognized as dates. You can reformat it, use it in formulas, etc.

Hope this helps.

chrisb84

[Hercules1946]

I think that you will need a formula to pick out the elements you need for the Date() function from the text.
Are all your dates formatted as you show, and if not how difficult would it be to extract the mmm/dd/yyyy part ?

[martindwilson]

until someone comes up with something better!
=DATE(--MID(A1,9,4),LOOKUP(LEFT(A1,3),{"apr","aug","dec","feb","jan","jul","jun","mar","may","nov","oct","sep"},{4,8,12,2,1,7,6,3,5,11,10,9}),--MID(A1,5,2))

[thejayjetson]

TRUE TRUE TRUE
Ladies and gents, we have a winner. Very smart Chris. I was able to perform this on the original column with no problem. I just had to put only one line directly after the year (while in Text-To-Columns), and the rest was a piece of cake.

-The Jay