Recognizing text format as date

The below suggestion will work if your system Date setting is DD/MM/YYYY

Assume that the data in A1 cell

A1 cell
July, 2012

In B1 cell

Formula:

=VALUE((1&A1))

Or

Formula:

=--(1&A1)

Will convert it as real date. Format the cell as date.

Hope this helps!

Originally Posted by Sindhus

@Sixthsense: Both the formula returns #value error. Have i missed anything?

Yes, you copied the OP's Text with line break and applied my suggestion.

But the actual text should be

Formula:

July, 2012

Now try the formula which i suggested.

I believe that OP's data don't have line break inside the cell. In that case the formula which i suggested will work fine.

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).