Hassan Shuvo
If you are facing problems converting numbers to dates in Excel, you are in the right place for a solution. Today, in this article, we will explain and demonstrate some easy ways you can convert any number to a date format (YYYYMMDD) in Excel.
📁 Download Excel File
Download this file to better understand.
Learn to Convert Number to Date Format (YYYYMMDD) in Excel with These 5 Methods
Before you can convert numbers to dates in Excel, you must first understand how Microsoft Excel maintains dates. While you may expect Excel to retain the day, month, and year for a date, this is not the case. Dates are stored in Excel as consecutive integers, and it is only the formatting of a cell that permits a number to be shown as a date. All dates are recorded as integers denoting the number of days from January 1, 1900 (number 1) to December 31, 9999 (number 2958465). Apart from the serial numbers, Excel can also convert 8-digit numbers written as YYYYMMDD to dates. But they require the use of functions like DATE, LEFT, RIGHT, MID, and TEXT.
In this article, we will show you how to convert these two types of number formatting to date format (YYYY-MM-DD) in Excel using 5 simple methods. But please remember, not every method applies to both formatting types. Before each method, we will make it clear which applies to which.
1. Formatting Cells Accordingly
Our first method will apply to serial numbers, which Excel starts counting from the first day in its library, which is January 1, 1900. Assume today is December 22, 2022, which is the 44917th day if I begin counting on January 1, 1900. Excel stores 44917 as the date. But to show it in a typical date format, we have to format the cells. Excel has a lot of varieties to choose from when selecting date formats.
You can select any format from the Format Cells window. Let’s see how we can choose one, step by step.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cells where you put your dates in serial numbers. Our Dates are in the range of B6:B11. So we selected those.
- Next press Ctrl+1 on your keyboard. The format cells window will appear. You can choose your date types.
- From the Number tab, go to Date and then choose your preferred type. We are choosing YYYY-MM-DD. Finally, click OK.
And done.
2. Applying DATE Function
If you are putting your numbers in YYYYMMDD format, you can use functions to change them. In this section, we will use the DATE, LEFT, MID, and RIGHT functions to convert the 8-digit numbers into date formatting. Remember, this method will give you an incorrect result if you use it for serial numbers. The DATE function generates a valid date by combining the components of the year, month, and day. The LEFT, MID, and RIGHT functions in Excel retrieve a specified number of characters from the left, middle, and right sides of a text string. Let’s make a formula using the functions.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your output cell. As for us, we selected cell C6 and wrote this formula in the Formula Bar.
🔨 Formula Breakdown
👉First, the LEFT, MID and RIGHT functions extract the number of characters from 8-digit numbers as we instructed.
👉Taking those characters, the DATE function formed our dates.
- Then when you hit Enter, you will see the date has converted to a serial number. We will have to format cells like the previous method to show Dates.
- Use the Fill Handle for the rest of the rows.
- Next select cells C6:C11 and press Ctrl+1 on your keyboard. The Format Cells window will appear.
- From the Number tab, go to Date and then choose your preferred type. We are choosing YYYY-MM-DD.
- Finally, click OK.
And Voila! You have some dates.
3. Using TEXT Function
We can also convert Excel serial number to date format (YYYYMMDD) by using the TEXT function. The TEXT function produces a number as text in the format specified. To embed formatted numbers into text, use this function. The downside is Excel will recognize it as a general text format and not as a date in some cases which may hinder further calculations.
⬇️⬇️ STEPS ⬇️⬇️
- We selected cell C6 cell to write our formula which is as follows.
- Next hit Enter to see the serial numbers as dates.
- Finally, use the Fill Handle tool to copy the formula to the rest of the rows.
4. Applying Text to Column Wizard
Additionally, Excel’s Text To Columns Wizard on the Data tab can convert 8-digit numbers to regular dates. The Text to Columns Wizard can be used to separate the contents of a single Excel cell into separate columns. This feature helps users identify certain delimiters, like commas and semicolons, in a text and separate the texts at those places. But we can also use this feature to convert 8-digit numbers to date format (YYYY-MM-DD).
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cells where you put in your numbers. We selected the range B6:B11.
- Next, go to the Data tab, and from the Data Tools group select Text to Columns.
- A window will pop up. In the Original data type section, check Delimited. Then click Next.
- In Delimiters, uncheck everything and then again press Next.
- In the final step in Column data format, change the date format as per your preference. We are choosing YMD as in the first year then month and then day. You can also choose the destination. If you want, you can keep the destination the same as your selection or change it to another cell. For demonstration purposes, we will select a different cell C6.
- Finally, click Finish to apply the changes.
And magic.
5. Using VBA Code
Finally, we have arrived at the final method for converting numbers to dates, in which we will employ the VBA script. Before writing VBA code, we enabled the Developer tab in our Ribbon. Find out how to access the Developer tab here.
These instructions will help you write your code when you access the Developer tab.
⬇️⬇️ STEPS ⬇️⬇️
- While on the Developer tab, click the Visual Basic button in the Ribbon’s left-hand corner.
- A window will show up where you will click Insert and, subsequently, Module.
- In the module, copy and paste the following formula.
Sub ConvertyyyymmddToDate()
Dim WB As Worksheet
Dim DataRange As Range
Set WB = Worksheets("VBA")
Set DataRange = WB.Range("B6:B11")
For Each x In DataRange
x.Value = DateSerial(Left(x.Value, 4), Mid(x.Value, 5, 2), Right(x.Value, 2))
x.NumberFormat = "yyyy-mm-dd"
Next x
End Sub
- To apply change press F5 on your keyboard or click on Run and click Run Sub/Userform.
As a result, our numbers will become dates.
How to Convert Text to Date in Excel
When you find dates in text format, you’ll probably want to convert them to regular Excel dates so you can use them in formulas to do various computations. Additionally, there are several ways to approach the issue, as is frequently the case in Excel. We will just show you one way you can convert text to date using the DATEVALUE function. The DATEVALUE function turns a text string-based date into a valid Excel date. For example, in the Excel date system, the following formula generates a serial number (44917) that reflects December 22, 2022.
Let’s use the formula in our example.
⬇️⬇️ STEPS ⬇️⬇️
- First, select your output cell. We selected cell C6 and typed in this formula.
- Next hit Enter to see the serial numbers.
- To change it to date format, select cell C6 and press Ctrl+1 on your keyboard. The Format Cells window will appear.
- From the Number tab, go to Date and then choose your preferred type. We are choosing YYYY-MM-DD.
- Finally, click OK.
- This converts the number to date format (YYYY-MM-DD).
- Finally, use the Fill Handle tool to copy the formatting to the rest of the rows.
📄 Important Notes
🖊️ While using the TEXT function remember to use serial numbers. If you use 8-digit numbers, Excel will show #######.
🖊️ The same thing goes for the DATE function. If you use serial numbers, they will give you incorrect results.
📝 Takeaways From This Article
📌 First, we showed how to convert numbers using the Format Cells window.
📌 Again, we used the DATE function for conversion.
📌 Next, the TEXT function was used to convert serial numbers into dates.
📌 Finally, we used VBA code to approach the problem.
Conclusion
This is the end of our article. We tried our best to cover all sorts of methods to make the process easier. We hope, you can now easily convert a number to date format (YYYYMMDD) in Excel. If you know of any other easy methods, please don’t be shy to share them with us in the comment section. For more tips and tricks for Excel, visit Excelden.com.
