View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ms-Exl-Learner Ms-Exl-Learner is offline
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 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)