How to Convert 8 Digit Number to Date in Excel (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Using Text to Columns Wizard to Convert 8 Digit Number to Date in Excel

Convert Text to Columns Wizard dialog box will appear.

❹ All you need to do is, hit the Next button with Delimited selected.

Using Text to Columns Wizard to Convert 8 Digit Number to Date in Excel

❺ Again click on the Next button in Step 2 of the Convert Text to Columns Wizard dialog box.

Using Text to Columns Wizard to Convert 8 Digit Number to Date in Excel

❻ Now select Date in the ‘column data format’ section.

❼ Then select YMD beside the Date option.

❽ Choose a Destination.

❾ Finally hit the Finish button.

Using Text to Columns Wizard to Convert 8 Digit Number to Date in Excel

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.

Combining DATE, LEFT, RIGHT, & MID Functions to Convert 8 Digit Number to Date in Excel

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

Combining DATE, LEFT, RIGHT, & MID Functions to Convert 8 Digit Number to Date in Excel

❸ Drag the Fill Handle button from cell C5 to C10.

Combining DATE, LEFT, RIGHT, & MID Functions to Convert 8 Digit Number to Date in Excel

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.

3. Using TEXT Function to  Convert 8 Digit Number to Date in Excel

❸ Drag the Fill Handle button from cell C5 to C10.

3. Using TEXT Function to  Convert 8 Digit Number to Date in Excel

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


<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

3 Comments
  1. 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.

  2. that was really helpful, many thanks
    adel ayyoub

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo