Converting general text format to date

[Sarah (OGI)]

I've received a report whereby column A shows a list of dates.
Unfortunately, it seems that the date is written in a 'general' format and
looks like this:

Sunday, June 15, 2008

Is there an easy way to convert this into a date format, i.e. dd/mm/yy?

 

[Gary''s Student]

With your value in A1 use:

=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))

and format it as you choose

 

[Rick Rothstein \(MVP - VB\)]

Assuming your date data in Column A starts at Row 2, put this formula in Row
2 of an unused (helper) column....

=--MID(A1,FIND(",",A1)+2,20)

and copy it down. Next, select this entire column and press Ctrl+C (or click
Edit/Copy on the menu bar), click on A2 and click Edit/PasteSpecial on the
menu bar, select the Values option under in the Paste section and click OK,
press escape to clear the operation and, finally, delete the helper column.
If your dates look like 5-digit numbers, select the column and use Format
Cells to format the displayed dates as you want.

Rick

 

[Sarah (OGI)]

Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas
why this might be?

 

[Gary''s Student]

The formula will work if the text starts with a day followed by a comma
followed by a single space. Which is what your example shows.

 

[Sarah (OGI)]

Yes, the text does start with a day followed by a comma followed by a single
space.
I've tried the formula you suggested again but have excluded the DATEVALUE
section (so I've entered 'RIGHT(A1,LEN(A1)-FIND(" ",A1,1))'), which gives the
result of:

June 15, 2008

As soon as I include the DATEVALUE, I get the #VALUE! result.

I've tried it on a new spreadsheet and a new session of Excel, just in case
there was a problem with the source data.

 

[Gary''s Student]

This is very interesting.

If you start with a clean worksheet, format cell A1 as Text and enter:

June 15, 2008

with only single spaces internally and no leading or trailing spaces. In
another cell, enter:

=DATEVALUE(A1)

you should see: 39614
which is the date in number format.