Prev: Excel - Calculate age from DOB in years, months days
Next: TransferSpreadsheet to colored fields
From: Datahead on 10 Feb 2010 10:31
how do I convert date format yyyymmdd to mmddyyy

I have rows of dates displayed as yyyymmdd (ie 20100131)

I want them displayed as regular dates (ie 01/31/2010)
From: Dave Peterson on 10 Feb 2010 10:41
If it's a column of cells...

Select the column
Data|text to columns (in xl2003 menus)
choose fixed width, but don't have any delimiter lines
Choose Date (ymd)
This will convert the data to dates.

Now you can format the ranyge the way you like.

Datahead wrote:
>
> how do I convert date format yyyymmdd to mmddyyy
>
> I have rows of dates displayed as yyyymmdd (ie 20100131)
>
> I want them displayed as regular dates (ie 01/31/2010)

--

Dave Peterson
From: Ms-Exl-Learner on 10 Feb 2010 11:14
I assume that you are having the data in A1 cell like the below:-

A1 cell
20100131

Paste this formula in B1 cell
=IF(A1="","",VALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4)))

Place the cursor in B1 cell and do Right Click>>Format
Cells>>Number>>Category>>Custom>>Type - paste the below format

mm/dd/yyyy

and Give Ok.

Change the formula cell reference A1 to your desired cell, if required

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Datahead" wrote:

> how do I convert date format yyyymmdd to mmddyyy
>
> I have rows of dates displayed as yyyymmdd (ie 20100131)
>
> I want them displayed as regular dates (ie 01/31/2010)
 | 
Pages: 1
Prev: Excel - Calculate age from DOB in years, months days
Next: TransferSpreadsheet to colored fields