Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have an Excel spreadsheet with over 10,000 entries. This spreadsheet contains two columns of dates, and I need to select only those entries where the dates do NOT match in the two columns.

The problem is, the dates have been differently formatted and are embedded within other information, like this: $obj->parsedDate('>=','1997','3','1')

Sometimes the two columns have used exactly the same formatting and I can exclude these using the IF function. However in many cases the date may have been entered differently in the second column, like $obj->parsedDate('>=',1997,3,1) vs $obj->parsedDate(">=",1997,3,1), so the numbers will be the same but some of the other characters will not not.

Can Excel look for just the numbers and identify matches in this way?

share|improve this question

1 Answer 1

Try this formula to convert the text into dates:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(",",A1)+1,99),")",""),"'",""),"""",""),",","-"))

It is built on the assumption that the first comma in all cells is just in front of the date, like all three dates in your data sample show. If that assumption is wrong, you may need to provide some other pointer that always exists in the data, so it can be converted.

Format the result with the data format of your choice. Apply this formula to both columns and a comparison will be easy.

enter image description here

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.