In execel how can I convert dates from yyyymmdd to mm/dd/yyyy

[Guest]

I have a column of dates that imports in yyyymmdd format, but I need it in
mm/dd/yyyy. How can I convert this without manually entering them?

 

[Guest]

select the column
Format>Cells
select - "Custom"
type in your format

m/d/yy
gives you 3/3/05 or 12/12/05

mm/dd/yyyy
gives you 03/03/2005 or 12/12/2005

 

[Bernie Deitrick]

For that date cell A1, use this formula in B1

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

On Fri, 23 Sep 2005 07:35:03 -0700, "converting date formats" <converting date

I have a column of dates that imports in yyyymmdd format, but I need it in
mm/dd/yyyy. How can I convert this without manually entering them?

If they are being interpreted as Excel dates, then just select the column and
format it as Format/Cells/Number/Custom Type: mm/dd/yyyy

If not, then you need to convert them to Excel dates.

Select the Column
Data/Text to Columns/Next/Next
Column Data Format Date: YMD
Finish

or you can use the formula

=--TEXT(A1,"--TEXT(A1,"0000\/00\/00")

and format as mm/dd/yyyy


--ron