Convert 20050118 to a working date field

I have exported this field using RIGHT, MID, LEFT, & functions but I am left
with a column that looks like 2005/01/18. I then paste values and when I
format as a date it does nothing until I press F2 for each cell. I have many
rows, is there an easier way to make the date field usable?

One way that might work, select all dates and do edit replace and replace /
with /
(yes repleace the forward slash with a forward slash)
another thing might be to copy an empty cell, select the dates and do
edit>paste special and select add,
then reformat as dates again yyyy/mm/dd

--

Regards,

Peo Sjoblom


"Jessica" <[email protected]> wrote in message
news:[email protected]...
> I have exported this field using RIGHT, MID, LEFT, & functions but I am
left
> with a column that looks like 2005/01/18. I then paste values and when I
> format as a date it does nothing until I press F2 for each cell. I have
many
> rows, is there an easier way to make the date field usable?

=TEXT(A1,"0000\-00\-00")+0

Format as date.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have exported this field using RIGHT, MID, LEFT, &
functions but I am left
>with a column that looks like 2005/01/18. I then paste
values and when I
>format as a date it does nothing until I press F2 for
each cell. I have many
>rows, is there an easier way to make the date field
usable?
>.
>

Select dates and do Data / Text To Columns, then in the wizard choose the
appropriate date option for your column

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jessica" <[email protected]> wrote in message
news:[email protected]...
> I have exported this field using RIGHT, MID, LEFT, & functions but I am
left
> with a column that looks like 2005/01/18. I then paste values and when I
> format as a date it does nothing until I press F2 for each cell. I have
many
> rows, is there an easier way to make the date field usable?

Thanks all! 2/3 answers worked!

"Jessica" wrote:

> I have exported this field using RIGHT, MID, LEFT, & functions but I am left
> with a column that looks like 2005/01/18. I then paste values and when I
> format as a date it does nothing until I press F2 for each cell. I have many
> rows, is there an easier way to make the date field usable?

Jason Morin wrote...
>=TEXT(A1,"0000\-00\-00")+0

Did you test this?

This would only work if the OP's cells contain 8-digit numbers
formatted as "0000\/00\/00". However, if the OP were generating these
cell values with LEFT, MID and RIGHT, then they're text, in which case
your formula fubars.

TEXT will treat A1 evaluating to "2005/02/23" as a value, but as a
*DATE* value. That is, TEXT("2005/02/23","0") would return 38406 (1900
date system), so with A1 evaluating to "2005/02/23" your formula would
evaluate to the rather unhelpful 0003-84-06.

Ken Wright wrote...
>Select dates and do Data / Text To Columns, then in the wizard choose
the
>appropriate date option for your column
....

Picky - Data > Text to Columns, select Fixed width then immediately
click Finish would also work.

Cheers Harlan - Never considered trying it without explicitly specifying
dates. Got me curious though, so I went back and tried it with a different
date format, ie YDM, eg 2005/18/01 and it wouldn't work, so I guess it's OK
as long as the date is formatted the way the machine expects to see it, but
otherwise you need to be explicit in telling it what format it's in.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Ken Wright wrote...
> >Select dates and do Data / Text To Columns, then in the wizard choose
> the
> >appropriate date option for your column
> ...
>
> Picky - Data > Text to Columns, select Fixed width then immediately
> click Finish would also work.
>