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

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.

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.

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:

• After that, press ENTER.

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

Finally, you will get all the Dates.

 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:

• Consequently, pressENTER,and you will get the result for B5.

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

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.

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.

• Secondly, keep all the tabs unselected and click Next 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.

Finally, you will see the result as shown below.

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.

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

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

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

• Then, choose Converting_Num_yyyymmdd_To_Date >> press Run.

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

• Now, select the range and click OK.

Lastly, you can see the result in your worksheet.

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.

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.

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.

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

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.

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

• Insert Date in Excel (7 Simple Methods)
• Enter Time in Excel (5 Methods)
• Concatenate Date and Time in Excel (4 Formulas)
• Remove Time from Date in Excel (6 Approaches)
• Combine Date and Text in Excel (5 Ways)
• Add Months to a Date in Excel (2 Ways)
• Count Months in Excel(5 ways)
• Excel Count Months in a Column( 4 Quick Ways)