8 digit date no slashes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dkingston - 30 Mar 2006 15:27 GMT

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.

Reply to this Message

Andy - 30 Mar 2006 15:31 GMT

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.

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

Reply to this Message

Dave Peterson - 30 Mar 2006 15:39 GMT

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)

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

Signature

Dave Peterson

Reply to this Message

dkingston - 30 Mar 2006 16:26 GMT

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?

> If they're already real dates, maybe you could just change the number format:
>
[quoted text clipped - 6 lines]
> > how can i accomplish this?
> > thanks in advance for your help.

Reply to this Message

Pete_UK - 30 Mar 2006 17:13 GMT

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

Reply to this Message

dkingston - 30 Mar 2006 18:37 GMT

that worked perfectly.
thanks everyone for the help.

> Assume your date is in A1 with an apostrophe in front, then enter this
> in B1:
[quoted text clipped - 7 lines]
>
> Pete

Reply to this Message

Pete_UK - 30 Mar 2006 21:39 GMT

Thanks for letting us know how you got on.

Pete

Reply to this Message

Harlan Grove - 30 Mar 2006 23:57 GMT

Pete_UK wrote...

>Assume your date is in A1 with an apostrophe in front, then enter this
>in B1:
[quoted text clipped - 3 lines]
>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.

Reply to this Message

Pete_UK - 31 Mar 2006 00:15 GMT

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

Reply to this Message