Recognizing text format as date

Originally Posted by Sindhus

No i didnt copy it. I typed it myself.

Now i copied your text and tried. Still returning error only

The problem is that the date setting of your system is MM/DD/YYYY, but my date setting is DD/MM/YYY.

In OP’s data "July, 2012" already the Month July is present so adding 1 (=--(1&A1)) with the "July, 2012" is like saying JanuaryJuly,2012 and converting it to real number with the text data is getting #VALUE! Error.

But in my case, (i.e.) my system date setting is DD/MM/YYY so adding 1 (=--(1&A1)) with the "July, 2012" is like saying 1July,2012 and converting it to real number with the text data is getting 1-Jul-12.

If you want to test it, then Control Panel>>Date&Time Setting>>Regional & Language Option>>Regional Option>>Customize>>Date>>Short Date Format (Date Setting) & Long Date Format (Date Setting).

Originally Posted by Sindhus

Oh got it thank you

You're welcome

I added the notification in my first post with Red Text so that OP will also aware of this issue.