Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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) |
#2
![]() |
|||
|
|||
![]()
Converting Date Formats
To convert the date format from yyyymmdd to mm/dd/yyyy, use the following formula: Code:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm/dd/yyyy") Explanation:
To convert the date format from yyyymmdd to mmddyyyy, use the following formula: Code:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mmddyyyy") I hope that helps!
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 ClickFormat CellsNumberCategoryCustomType - 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how do I change the date format from yyyymmdd to mm/dd/yyyy | Excel Worksheet Functions | |||
How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy | Excel Worksheet Functions | |||
convert date mm/dd/yyyy to dd/mm/yyyy | Excel Worksheet Functions | |||
convert date (YYYYMMDD) to weeknumber | Excel Worksheet Functions | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel |