How to Convert Number (YYYYMMDD) to Date Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

You might have the date in your dataset in a format like YYYYMMDD (e.g., today’s date is 20211128) or, as the count of total days since January 1, 1900 (e.g., today is 44528). You would like to convert it to built-in Excel date formats. Amazingly, there are all types of Date formats available in Excel. The article will explain 4 methods to convert a number (yyyymmdd) to the Date Format in Excel.

Below, we have attached an overview image to make it clearer.

Convert Number (YYYYMMDD) to Date Format in Excel


Download Practice Workbook

You can download the practice workbook from here.


4 Methods to Convert Number (YYYYMMDD) to Date Format in Excel

Here, we will use the following dataset to explain the 4 different methods of conversion of number to date format in Excel.

Dataset for Converting Date Format

As you can see that the dataset contains two columns, one with numbers in the format yyyymmdd, and the other with the number of days since 1st January 1900. So, we will be using these dataset numbers to convert into date formats.

Furthermore, for conducting this session, we’re going to use Microsoft 365 version.


1. Using TEXT Function to Convert Number (YYYYMMDD) to Excel Date Format

Here, we can use a formula with the TEXT function to get the converted results. Basically, we must apply this method to the data which contains the number of days since 1st January 1900. Thus, we have used the 2nd column from the dataset.
Now, follow the steps below.

Steps:

  • First, you must write the formula in Cell C5. Here, the formula:
=TEXT(B5,"mm/dd/yyyy")

Use of TEXT Function Convert Number to Date Format in Excel

  • After that, press ENTER.

🔎 How Does the Formula Work?

📌 The TEXT formula takes the text which is B5 in this case as the first argument.

📌 Then, the second argument is format_text which contains the formatting which we want to include. Here, we have used “mm/dd/yyyy” as the format. You can use any format you wish within the apostrophe.

Converting Number to Date Format with Excel TEXT Function

  • Lastly, drag the Fill Handle icon until the end to get the result for all the data.

Applying Fill Handle Icon

Finally, you will get all the Dates.

Copy the Formula with TEXT Function to all Columns.


 2. Combining DATE, RIGHT, LEFT & MID Functions to Convert Number (YYYYMMDD) to Date

Let us get introduced to another formula to convert the numbers into Dates. Here, we will be using the numbers in the format yyyymmdd for this method.
The DATE function can be nested with RIGHT, LEFT and MID functions to convert numbers to dates in format “mm/dd/yyyy”.
Now, follow the steps below to apply the formula and get the desired result.

Steps:

  • Firstly, write the following formula in Cell C5:
=DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2))
  • Consequently, pressENTER,and you will get the result for B5.

Combining DATE, LEFT, MID and RIGHT Functions

🔎 How Does the Formula Work?

📌 Here the LEFT, RIGHT, and MID functions first argument is text. For this case, it is B5 that is it takes the text which needs to go through the conversion process.

📌 Since yyyymmdd is the format of the dataset, here the leftmost number up to 4 denotes the year, middle 2 is the month and the right 2 is the date.

📌 Accordingly, the functions contain numbers in their second argument. For the LEFT formula, it is 4, for RIGHT is 2, and MID, it takes 2 more arguments one from where it starts and the character up to which it will take.

📌 Finally all the three functions are nested with the DATE function to get the result.

  • Then, drag the Fill Handle icon to the end of the dataset to get results for the rest of the data.

Lastly, you will find the result below.

Convert Number (YYYYMMDD) to Date Using Excel Functions


3. Applying Text to Columns Wizard for Converting Number (YYYYMMDD) to Date Format

However, another Excel tool, the Text to Columns from the Data tab, can also be used to convert the number to date format.
So, to apply this, you need to follow the below steps.

Steps:

  • At first, select the dataset which you want to convert to Date format.
  • Then, select Text to Columns from the Data Tools of the Data tab.

Applying Text to Columns Feature to Convert Number (YYYYMMDD) to Date Format

There are 3 steps in the Text to Columns Wizard:

  • Firstly, choose Delimited and click Next in the Convert Text to Columns Wizard – Step 1 of 3.

Pressing NEXT Button to the Convert Text to Columns Wizard - Step 1 of 3

  • Secondly, keep all the tabs unselected and click Next in the Convert Text to Columns Wizard – Step 2 of 3.

Uncheck all in the Convert Text to Columns Wizard - Step 2 of 3

  • Thirdly, select Date and from the drop-down menu select YMD >> select Destination >> click Finish in the Convert Text to Columns Wizard – Step 3 of 3.

Pressing FINISH Button to the Convert Text to Columns Wizard - Step 3 of 3

Finally, you will see the result as shown below.

Results for Using Text to Columns Feature


4. Using VBA Macro to Convert Number (YYYYMMDD) to Date Format

The last method of this article is using VBA Macro to convert numbers in the format YYYYMMDD to Date format.

Here, you can apply VBA macro code to convert numbers to date format through the following steps.

Steps:

  • Firstly, go to your worksheet.
  • Secondly, press ALT+F11 from your keyboard. This will open the VBA Macro window.
  • Thirdly, from the Insert tab choose the Module.

Inserting Module

This will open a General window.

  • Now, you must write the code below in the General window.
Sub Converting_Num_yyyymmdd_To_Date()
Dim my_numbr As Range
Dim my_selected_range As Range
On Error Resume Next
xTitleId = "Converting Number to Date with Exceldemy"
Set my_selected_range = Application.Selection
Set my_selected_range = Application.InputBox("Range", xTitleId, my_selected_range.Address, Type:=8)
   For Each my_numbr In my_selected_range
       my_numbr.Value = DateSerial(Left(my_numbr.Value, 4), Mid(my_numbr.Value, 5, 2), Right(my_numbr.Value, 2))
       my_numbr.NumberFormat = "mm/dd/yyyy"
   Next
End Sub

VBA Code for Converting Number (YYYYMMDD) to Date Format in Excel

Code Breakdown

  • Here, we have created a Sub Procedure named Converting_Num_yyyymmdd_To_Date.
  • Next, we declare two variables my_numbr , and my_selected_range as Range.
  • Then, we have used a dialog box to select the data.
  • After that, we used a For Each Loop to apply the formula in every cell of selection.

  • After that, save the file as Excel Macro-Enabled Workbook and go back to worksheet.
  • Afterward, from Developer tab >> select Macros.

Clicking Macros to Run Code

As a result, you will find the dialog box named Macro.

  • Then, choose Converting_Num_yyyymmdd_To_Date >> press Run.

Run the Code

At this time, you will see the dialog box named Converting Number to Date with Exceldemy.

  • Now, select the range and click OK.

Select Range to Convert Number (YYYYMMDD) to Date Format in Excel

Lastly, you can see the result in your worksheet.

Converted Number (YYYYMMDD) to Date Format with VBA Code

Note: This method will show results within the selected data. Hence your dataset will be lost after the application of this method. If you need them in the future, then copy and paste them somewhere else.

Different Available Formats of Date in Excel

Excel has a lot of Date formats available that truly serve the users. So, you can also utilize them by following the steps below.

Steps:

  • At first, select the data.
  • Then, press CTRL+1 from the keyboard.

Pressing CTRL+1 to Open Format Cells Dialog Box

This will open a box named Format Cells where you must choose Dates from Numbers.

  • Then you can see varieties of formats for dates. Here, you may select anyone you want.
  • Then click OK.

Available Types of Date in Format Cells Feature

Or,

  • After selecting data from the Home tab select drop-down in the Number section.
  • Furthermore, choose More Number Formats. This will also open the box where you will find varieties of formats for dates.

Available Number Formats in Excel

For example, we have shown 3 different formats in the below picture.

Different Date Formats in Excel


Stop Auto Conversion of Numbers to Dates

Microsoft updates MS-Excel regularly to make it more user-friendly. One feature relevant to it is, sometimes it auto converts numbers to dates. If you want to ignore that and keep your data in Number form, there are a few tips for you.

1. You can use a space or apostrophe before the number you want to type. The apostrophe is preferable as space might hamper the efficiency of a few other Excel functions.

2. Again, you can select the cells where you will type your data and press CTRL+1. This will open the Format Cells box. Select Text from there and click OK. This will stop the auto-conversion and keep your data in text form.


Things to Remember

You must know the usage of each method and then follow the steps carefully to get results as per your requirements.


Practice Section

Now, you can practice by yourself.

Practice Section to Convert Number to Date Format in Excel


Conclusion

The article evaluates 4 different methods to convert number to date formats in Excel. We have used Excel formulas like DATE, MID, TEXT, and so on and Excel tools like Text to Column and VBA Macro codes for the methods. We hope the article was helpful to you. For any further queries, you can write in the comment section. Also, you can visit our website Exceldemy to learn more Excel-related content. 


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

2 Comments
  1. Your copy/paste formula in the 2nd method is incorrect:

    =DATE(LEFT(B5,4),MID(B5,3,2),RIGHT(B5,4))

    In your screenshot, it is right as:

    =DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,4))

    FYI.

    • Greetings LIBBY,
      Thank you for letting us know. We’ve updated it.
      Feel free to comment if you have further inquiries. We are here to help.
      Regards
      Arin Islam (Exceldemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo