8 digit date no slashes

[Guest]

i need to convert a list of dates from 1/1/2006 format to 01012006 format.
how can i accomplish this?
thanks in advance for your help.

 

[Guest]

Hi

Select your column of dates and go to Data/Text to Columns. On the third
dialog box (I think) it gives an option to tell Excel that the column
contains dates - and also the format of those dates.
Backup your data before you start.

Andy.

 

[Dave Peterson]

If they're already real dates, maybe you could just change the number format:

Select the list
format|Cells|number tab|custom category
mmddyyyy
(or ddmmyyyy--difficult to tell with your sample date)

 

[Guest]

thanks for the help so far. for clarification my problem is...
i have a column containing dowloaded dates in mm/dd/yyyy format; i.e.
january 1, 2007 = '1/1/2007
i need to upload the dates in mmddyyyy format; i.e. january 1, 2007 = 01012007

i'm familiar w/ excel but far from expert, i don't know if the apostrophe is
causing my problems or how to get rid of it
if i could remove the apostrophe i could reformat to mm/dd/yy where excel
will not drop leading zeros, parse the thing into 3 columns, add 2000 to the
year column and reassemble them to the format i need

sooooo...how do i remove the apostrophe from the column of dates?

 

[Pete_UK]

Assume your date is in A1 with an apostrophe in front, then enter this
in B1:

=VALUE(A1)

and apply a custom format to this cell as mmddyyyy. This should give
you what you want. Copy down as necessary.

Hope this helps.

Pete

 

[Guest]

that worked perfectly.
thanks everyone for the help.

 

[Pete_UK]

Thanks for letting us know how you got on.

Pete

 

[Harlan Grove]

Pete_UK wrote...

Assume your date is in A1 with an apostrophe in front, then enter this
in B1:

=VALUE(A1)

and apply a custom format to this cell as mmddyyyy. This should give
you what you want. Copy down as necessary.

Actually, Andy's suggestion in the first response in this thread, using
Text to Columns, would have converted the date strings to date values.
They'd still need to be formatted as mmddyyyy, but no additional column
of formulas is needed.

 

[Pete_UK]

I agree.

But the OP's second post, nearly an hour after Andy's post, asked how
to get rid of the apostrophe and also mentioned parsing into 3 columns.
I gave him an alternative.

Pete