Method 1 – Using the Text to Columns Wizard to Convert 8-Digit Numbers to Dates
- Select the numbers.
- Go to the Data tab.
- Select the Text to Columns command in the Data Tools drop-down.
The Convert Text to Columns Wizard dialog box will appear.
- Hit the Next button with Delimited selected.
- Click on the Next button in Step 2 of the Convert Text to Columns Wizard dialog box.
- Select Date in the ‘column data format’ section.
- Select YMD in the Date option.
- Choose a Destination.
- Hit the Finish button.
All the 8-digit numbers will be converted into dates in the specified destination. The date format is MM/DD/YYYY.
Read More: How to Convert Number to Date in Excel
Method 2 – DATE, LEFT, RIGHT, and MID Functions to Convert Numbers into Dates
Case 2.1 – 8-Digit Number in YYYYMMDD Format
- Insert the following formula in cell C5.
=DATE(LEFT(B5,4),MID(B5,5,2),(RIGHT(B5,2)))- Press the Enter button.
Formula Breakdown
- LEFT(B5,4) extracts the 4 digits of the 8-digit number from its left-most side.
- MID(B5,5,2) extracts 2 digits of the given 8-digit number from its 5th digit.
- RIGHT(B5,2) extracts 2 digits of the 8-digit number from its right side.
- DATE(LEFT(B5,4),MID(B5,5,2),(RIGHT(B5,2))) here the DATE function converts the numbers returned by LEFT(B5,4), MID(B5,5,2), RIGHT(B5,2) into a date format of DD/MM/YY.
- Drag the Fill Handle button from cell C5 to C10.
You will see that the 8-digit numbers with YYYYMMDD format have been converted into dates of format DD/MM/YYYY.
Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel
Case 2.2 – 8-Digit Numbers in the DDMMYYYY Format
- Insert the following formula in cell C5.
=DATE(RIGHT(B5,4),MID(B5,3,2),LEFT(B5,2))- Press the Enter button.
Formula Breakdown
- LEFT(B5,2) extracts 2 digits of the 8-digit number from its left-most side.
- MID(B5,3,2) extracts 2 digits of the given 8-digit number from its 3rd digit.
- RIGHT(B5,4) extracts 4 digits of the 8-digit number from its right side.
- DATE(RIGHT(B5,4),MID(B5,3,2),LEFT(B5,2)) here the DATE function converts the numbers returned by LEFT(B5,2), MID(B5,3,2), RIGHT(B5,4) into a date format of DD/MM/YY.
- Drag the Fill Handle button from cell C5 to C10.
You will see that the 8-digit numbers with DDMMYYYY format have been converted into dates of format DD/MM/YYYY.
Read More: How to Convert General Format to Date in Excel
Method 3 – Using the TEXT Function to Convert 8-Digit Numbers to Dates
- Insert the following formula in cell C5.
=TEXT(B5,"0000\/00\/00")Formula Breakdown
Cell B5 has an 8-digit number to convert. The TEXT function converts the number in cell B5 into the format “0000\/00\/00”. This format means that the output date format will be in YYYY/MM/DD.
- Press the Enter button.
- Drag the Fill Handle button from cell C5 to C10.
- You will see that the 8-digit numbers with YYYYMMDD format have been converted into dates of format YYYY/MM/DD.
Read More: How to Convert Serial Number to Date in Excel
Practice Section
You will get an Excel sheet like the following screenshot at the end of the provided Excel file ,where you can practice all the methods discussed in this article.
Download the Practice Workbook
Related Articles
- How to Convert Text to Date in Excel
- How to Convert Text to Date and Time in Excel
- How to Convert Text Date and Time to Date Format in Excel
- Text Won’t Convert to Date in Excel
<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Well done. Appreciate the tutorial and showing us multiple methods. I have been using the Date plus LEFT and MID functions in the past. Your third option is more efficient and effective. Thank you for helping me save time.
Thanks for your feedback, Carol! Best regards.
that was really helpful, many thanks
adel ayyoub
Very helpful. Thank you.
Hello Rhonda Rodriguez,
Thanks for your appreciation. You are most welcome.
Regards
ExcelDemy