Sometimes, you have a column of data that contains dates in the form of numbers, and you need to convert those numbers into dates. If you have a column of 8-digit numbers in Excel, you can convert those numbers into dates using several methods. In this article, I will show you 3 effective ways to convert an 8-digit number to date in Excel with ease. So, without having any further discussion, let’s dive straight into it.
How to Convert 8 Digit Number to Date in Excel: 3 Ways
1. Using Text to Columns Wizard to Convert 8 Digit Number to Date
Excel has a built-in feature to convert an 8-digit number into a date. This feature is known as the Text to Columns wizard. Here I will show you how to use this tool to convert an 8-digit number to a date in Excel.
❶ At first, you need to highlight all the numbers.
❷ Then go to the Data tab.
❸ Now select the Text to Columns command in the Data Tools drop-down.
Convert Text to Columns Wizard dialog box will appear.
❹ All you need to do is, hit the Next button with Delimited selected.
❺ Again click on the Next button in Step 2 of the Convert Text to Columns Wizard dialog box.
❻ Now select Date in the ‘column data format’ section.
❼ Then select YMD beside the Date option.
❽ Choose a Destination.
❾ Finally hit the Finish button.
Finally, all the 8-digit numbers will be converted into dates in the specified destination. Here, the date format is MM/DD/YYYY.
Read More: How to Convert Number to Date in Excel
2. DATE, LEFT, RIGHT, & MID Functions to Convert Number into Date
Here I will use a formula with the DATE, LEFT, RIGHT, & MID functions to convert an 8-digit number into a date in Excel. I will provide 2 different formulas whether the given numbers are in YYMMDD format or DDMMYY format, you can tackle it.
2.1. 8 Digit Number into YYMMDD Format
Suppose, the numbers are given in YYMMDD format. To convert these numbers into dates, follow the steps below.
❶ First, insert the following formula in cell C5.
=DATE(LEFT(B5,4),MID(B5,5,2),(RIGHT(B5,2)))❷ Then 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.
Finally, you will see that the 8-digit numbers with YYMMDD format have been converted into dates of format DD/MM/YY.
Read More: How to Convert Number (YYYYMMDD) to Date Format in Excel
2.2. 8 Digit Number into DDMMYY Format
Suppose, the numbers are given in DDMMYY format. To convert these numbers into dates, follow the steps below.
❶ First, insert the following formula in cell C5.
=DATE(RIGHT(B5,4),MID(B5,3,2),LEFT(B5,2))❷ Then 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.
Finally, you will see that the 8-digit numbers with DDMMYY format have been converted into dates of format DD/MM/YY.
Read More: How to Convert General Format to Date in Excel
3. Using TEXT Function to Convert 8 Digit Number to Date
Here, I will show you how to convert an 8-digit number to a date using the TEXT function.
Now follow the steps below:
❶ Insert the following formula in cell C5.
=TEXT(B5,"0000\/00\/00")Formula Breakdown
Here, 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.
❷ Now press the ENTER button.
❸ Drag the Fill Handle button from cell C5 to C10.
Finally, you will see that the 8-digit numbers with YYMMDD format have been converted into dates of format YY/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 topics discussed in this article.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 3 ways to convert an 8-digit number to date in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
Related Articles
- How to Disable Auto Convert to Date in Excel
- 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