A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how do I convert date format yyyymmdd to mm/dd/yyyy



 
 
Thread Tools Display Modes
  #1  
Old February 10th 10, 04:31 PM posted to microsoft.public.excel.worksheet.functions
Datahead
external usenet poster
 
Posts: 1
Default how do I convert date format yyyymmdd to mm/dd/yyyy

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)
Ads
  #2  
Old February 10th 10, 04:41 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 35,220
Default how do I convert date format yyyymmdd to mm/dd/yyyy

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
  #3  
Old February 10th 10, 05:14 PM posted to microsoft.public.excel.worksheet.functions
Ms-Exl-Learner
external usenet poster
 
Posts: 506
Default how do I convert date format yyyymmdd to mm/dd/yyyy

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)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I change the date format from yyyymmdd to mm/dd/yyyy Charlene Excel Worksheet Functions 4 September 16th 09 08:13 PM
How do I convert a birthdate format from yyyy/mm/dd to mm/dd/yyyy Amy Ann Excel Worksheet Functions 3 December 13th 07 09:07 PM
convert date mm/dd/yyyy to dd/mm/yyyy maryj Excel Worksheet Functions 2 March 20th 07 08:38 PM
convert date (YYYYMMDD) to weeknumber mark paul Excel Worksheet Functions 1 February 27th 07 01:05 PM
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel Jack Wilson New Users to Excel 4 July 18th 06 01:57 PM


All times are GMT +1. The time now is 08:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.