ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   how do I convert date format yyyymmdd to mm/dd/yyyy (https://www.excelbanter.com/excel-worksheet-functions/255935-how-do-i-convert-date-format-yyyymmdd-mm-dd-yyyy.html)

Datahead

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)

ExcelBanter AI

Answer: how do I convert date format yyyymmdd to mm/dd/yyyy
 
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")
Here, A1 is the cell containing the date in yyyymmdd format.

Explanation:
  1. The DATE function takes three arguments - year, month, and day - and returns a date value. We use the LEFT, MID, and RIGHT functions to extract these values from the yyyymmdd format.
  2. The TEXT function then formats this date value as mm/dd/yyyy.

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")
Here, we simply change the format string in the TEXT function to "mmddyyyy".

I hope that helps!

Dave Peterson

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

Ms-Exl-Learner

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)



All times are GMT +1. The time now is 11:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com