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

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.


Download Practice Workbook

You can download the practice workbook from here.


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

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

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

Here 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. We will be using these dataset numbers to convert into date formats.


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

We can use a formula with the TEXT function to the converted results. 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.
Follow the steps below.

Steps:

  • You must write the formula in Cell C5. The formula:
=TEXT(B5,"mm/dd/yyyy")
  • After that, press Enter.
  • Lastly, drag the fill handle icon until the end to get the result for all the data.

Using TEXT formula  to Convert Number (YYYYMMDD) to Date Format

🔎 How Does the Formula Work?

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

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


 2. Using DATE with RIGHT, LEFT, and MID formulas to Convert Number (YYYYMMDD) to Excel Date Format

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

Steps:

  • Write the following formula in Cell C5:
=DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2))
  • Press Enter and you will get the result for B5.
  • Drag the fill handle icon to the end of the dataset to get results for the rest of the data.

You will find the result below.

Using DATE with RIGHT, LEFT, and MID formulas to Convert Number (YYYYMMDD) to Date Format

🔎 How Does the Formula Work?

📌 Here the LEFT, RIGHT, and MID formulas 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 formulas contain numbers in their second argument. For the LEFT formula, it is 4, for RIGHT 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 formulas are nested with the DATE formula to get the result.


3. Using Text to Column Wizard to Convert Number (YYYYMMDD) to Date Format

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

Steps:

  • Select the dataset which you want to convert to Date format.

Using Text to Column Wizard to Convert Number (YYYYMMDD) to Date Format

  • Select Text to Column from the Data Tools of the Data tab.

Selecting Text to Column from Data Tab

  • There are 3 steps in the Text to Column Wizard:

Step 1/3: Choose Delimited and click Next.

Step1 of 3 fin the Text to Column wizard

Step 2/3: Keep all the tabs unselected and click Next.

Step 2 of 3 in the Text to Column Wizard

Step 3/3: Select Date and from the drop-down menu select YMD. Lastly, click Finish.

Step 3 of 3 in the Text to Column Wizard

Finally, you will see the result as shown below.

Result of using Text to Column


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.

You can apply VBA macro code to convert numbers to date format through the following steps:

Steps:

  • Select the dataset you want to convert.

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

  • Press ALT+F11 from your keyboard. This will open the VBA Macro window.
  • Select your worksheet. Then from the Insert tab choose the Module.

Opening module in VBA window

This will open a General window.

General window view

  • Now, you must write the code below in the General window.

Code:

Sub ConvertyyyymmddToDate()
Dim x As Range
Dim Workx As Range
On Error Resume Next
xTitleId = "Date Format in Excel"
Set Workx = Application.Selection
Set Workx = Application.InputBox("Range", xTitleId, Workx.Address, Type:=8)
For Each x In Workx
x.Value = DateSerial(Left(x.Value, 4), Mid(x.Value, 5, 2), Right(x.Value, 2))
x.NumberFormat = "mm/dd/yyyy"
Next
End Sub

VBA Macro code to convert number to date in excel

  • Save the file as Excel Macro-Enabled Workbook.
  • Afterward, press F5 from the keyboard.

This will open a small box.

  • Check the range of the selected data and click OK.

Checking range to get the result

You can see the result in your worksheet.

Result of using VBA macro

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. You can also utilize them by following the steps below.

Steps:

  • Select the data.

Different Available Formats of Date in Excel

  • Press CTRL+1 from the keyboard.
  • This will open a box where you must choose Dates from Numbers.
  • Then you can see varieties of formats for dates. Select anyone you want.
  • Then click OK.

Showing varieties of date formats in format cell box

Or,

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

Drop-down menu from Number option in Home tab

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

3 examples of variation od 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 Cell 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.


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. I hope the article was helpful to you. For any further queries, you can write in the comment section.


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

ExcelDemy
Logo