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:
=TEXT(B5,"mm/dd/yyyy")
- 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.
- 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:
=DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B5,2))
- Consequently, pressENTER,and you will get the result for B5.
🔎 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.
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
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.
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)
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)