About Us

ExcelBanter AI

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:

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.
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!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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	5	May 6th 23 07:46 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 08:07 PM
convert date mm/dd/yyyy to dd/mm/yyyy	maryj	Excel Worksheet Functions	2	March 20th 07 07:38 PM
convert date (YYYYMMDD) to weeknumber	mark paul	Excel Worksheet Functions	1	February 27th 07 12: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