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?

With your value in A1 use:

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

and format it as you choose
--
Gary''s Student - gsnu200793


"Sarah (OGI)" wrote:

> 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?

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)" <(E-Mail Removed)> wrote in message
news:57829F88-3BA5-443A-B139-(E-Mail Removed)...
> 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?

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

"Gary''s Student" wrote:

> With your value in A1 use:
>
> =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
>
> and format it as you choose
> --
> Gary''s Student - gsnu200793
>
>
> "Sarah (OGI)" wrote:
>
> > 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?

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.
--
Gary''s Student - gsnu200793


"Sarah (OGI)" wrote:

> Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas
> why this might be?
>
> "Gary''s Student" wrote:
>
> > With your value in A1 use:
> >
> > =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
> >
> > and format it as you choose
> > --
> > Gary''s Student - gsnu200793
> >
> >
> > "Sarah (OGI)" wrote:
> >
> > > 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?

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" wrote:

> 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.
> --
> Gary''s Student - gsnu200793
>
>
> "Sarah (OGI)" wrote:
>
> > Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas
> > why this might be?
> >
> > "Gary''s Student" wrote:
> >
> > > With your value in A1 use:
> > >
> > > =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
> > >
> > > and format it as you choose
> > > --
> > > Gary''s Student - gsnu200793
> > >
> > >
> > > "Sarah (OGI)" wrote:
> > >
> > > > 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?

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.

--
Gary''s Student - gsnu200793


"Sarah (OGI)" wrote:

> 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" wrote:
>
> > 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.
> > --
> > Gary''s Student - gsnu200793
> >
> >
> > "Sarah (OGI)" wrote:
> >
> > > Thanks for your prompt response, but I'm getting a #VALUE! result. Any ideas
> > > why this might be?
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > With your value in A1 use:
> > > >
> > > > =DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)))
> > > >
> > > > and format it as you choose
> > > > --
> > > > Gary''s Student - gsnu200793
> > > >
> > > >
> > > > "Sarah (OGI)" wrote:
> > > >
> > > > > 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?