ExcelInsider Team
In Excel, the date is usually formatted as the format used in the operating system. However, that format is often not enough for a lot of tasks we need to do on Excel. For better analysis of data, it is required to convert the date into text format. In this article, we will learn how to excel convert date to text yyyymmdd.
➤ Take a helper cell to write the new formatted date.
➤ Copy this formula to that cell:
=TEXT(C2,”yyyymmdd”)
➤ Replace C2 with the cell where your date is and press Enter.
You might be interested to know how the formula works in this context. There are also some other methods that can do the same job with precision. To learn more about converting dates to text, keep reading the tutorial until the end.
Converting Using the TEXT Function
In this dataset, we have some data about customer satisfaction and the dates when the feedback was recorded. This dataset is supposed to be forwarded to another organization who asked the dates to be formatted in yyyymmdd format.
This is how we do it:
➤ Take a helper column to the right of the dates so that we can autofill the rows easily.
➤ Insert this formula in the first cell under the heading:
=TEXT(C2,”yyyymmdd”)
➤ Autofill the other cells by dragging the mouse.
Using YEAR-MONTH-DAY with the CONCATENATE Function
In this method, we will parse the year, month, and day separately, and then concatenate all of them to get the final output.
➤ Use this formula in the helper cell like before:
=CONCATENATE(YEAR(C2), TEXT(MONTH(C2), “00”), TEXT(DAY(C2), “00”))
➤ Replace C2 with the input cell.
Writing VBA Code
This time, we will not take the help of any existing functions. Instead, we will create our own formula to help us do the job. Follow the instructions below:
➤ While in the desired spreadsheet, press Alt+F11. A new window will open.
➤ From the Insert menu, click on Module.
➤ In the code window, write this code:
Function ConvertDateToText(dateValue As Date) As String
ConvertDateToText = Format(dateValue, "yyyymmdd")
End Function
➤ Go back to the spreadsheet, and write this formula in the helper cell:
=ConvertDateToText(C2)
➤ You know this part already. You have to replace the input cell C2 with your one and fill the other cells.
Changing Number Format
In this case, we are making use of the default formatting tools available in Excel. Follow the step-by-step guide below:
➤ Select the cells you have to work with.
➤ From the Home tab, go to Number > More Number Formats…
➤ From the new dialog box, select Custom from the left panel, and write yyyymmdd in the “Type:” text box.
➤ Press OK and see the result.
➤ At this point, the data is still formatted as numbers, although it shows up like it’s text. To convert the data, we select all of them and press Ctrl+C to copy.
➤ Now we open notepad and paste them by pressing Ctrl+V.
➤ Cut them from the notepad using Ctrl+A to select all and Ctrl+X to copy.
➤ Paste them in another column in excel.
➤ Go to Number Format and select Text. Now it is formatted properly.
Frequently Asked Questions
qqqWhy Excel convert text to date?
Excel recognizes dates easily, so you don’t have to convert them later. However, if you want to keep it as text, just put an apostrophe (`) before the text. If that is not possible, you can change the format of the cells by pressing Ctrl+1 and selecting Text.
How to convert date mm dd yyyy to dd mm yyyy in Excel?
Use the TEXT function:
=TEXT(C2,”ddmmyyyy”)
Replace C2 with your date cell.
How to turn yyyymmdd into date in Excel?
A combination of functions can be used to do that:
=DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)
Replace C2 with the input cell.
How do you convert text date to date in sheets?
Here is the formula for doing that:
=DATEVALUE(C2)
Replace C2 with the text value or the cell.
What is the formula between dates in Excel?
You can use regular subtraction as Excel stores dates as numbers. The formula will be like this:
=C2-B2
Here, C2 will be the future date, and B2 will be the past date.
How to convert data to text in Excel?
Select the data you want to convert. Upon pressing Ctrl+1, the Format Cells dialog box will open. From there, select Text from the left panel and press OK. The data you selected before should be converted to text.
Wrapping Up
In this article, we have learned four methods to convert a date to text in excel using yyyymmdd format. The workbook used in this tutorial can be downloaded for your convenience. If you like the article, bookmark the site and come back for more excel tutorials.
