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!