D
dkingston
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.
how can i accomplish this?
thanks in advance for your help.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
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.
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.
D
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)
Select the list
format|Cells|number tab|custom category
mmddyyyy
(or ddmmyyyy--difficult to tell with your sample date)
D
dkingston
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?
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?
P
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
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
D
dkingston
that worked perfectly.
thanks everyone for the help.
thanks everyone for the help.
P
Pete_UK
Thanks for letting us know how you got on.
Pete
Pete
H
Harlan Grove
Pete_UK wrote...
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.
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.
P
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
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