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

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.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


3 Ways to Convert 8 Digit Number to Date in Excel

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 4 Digit Number to Time in Excel (3 Methods)


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.


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 5 Digit Number to Time in Excel (4 Quick Tricks)


Similar Readings


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

Here, I will show you 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 Number to Minutes in Excel (2 Easy Methods)


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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

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

ExcelDemy
Logo