Md Shamse Tabrej
In this article, I’ll show you three excellent methods on how to excel convert date to text (yyyymmdd). Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Convert Date to Text (YYYYMMDD) in Excel with These 6 Useful Techniques
Here in this article, we will learn how to compare addresses in Excel using different approaches. To accomplish that we will consider a dataset containing a list of government holiday dates.
To be exact, I’ve provided a total of 6 methods down below.
1. Using TEXT Function
In this approach, we will use the TEXT function to convert a Date format to text string of (yyyymmdd) format. The steps are given below:
⬇️⬇️ STEPS ⬇️⬇️
- Select cell C6.
- Write down the following formula:
=TEXT(B6, “yyyy-mm-dd”)
- Press Enter key and drag the fill handle to see the results.
Thus you can convert the date to a text string in (yyyymmdd) format.
Read More: How to Convert Number to Text with 2 Decimal Places in Excel
2. Apply Custom Formatting
Now, we will try a different approach. We will not adopt any formula rather we will try the Format Cells option. The procedures are described step by step.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select the cells which you will want to Format.
- Secondly, go to Format Cells by clicking right on the mouse.
- And select the Custom option.
- Subsequently, insert yyyy-mm-dd in the typed dialogue and click OK.
- Finally, hit Enter key to see the results.
In this way, you can convert the date to a text string in (yyyymmdd) format.
Read More: 4 Ways to Convert Number to Text and Keep Trailing Zeros in Excel
3. Utilizing TODAY and TEXT Functions Combined
Now we will use both the TEXT and TODAY functions to convert a date format to a text string. The steps are given here:
⬇️⬇️ STEPS ⬇️⬇️
- Pick a blank cell.
- Insert the following formula.
=TEXT(TODAY(), “yyyy-mm-dd”)
- Press Enter to see the results.
You can convert a Date to a text string in the format (yyyymmdd) using this method.
4. Implementing Copy and Paste Technique
In a nutshell, this is the quickest method for converting dates to text.
⬇️⬇️ STEPS ⬇️⬇️
- Select and copy the cells you want to convert.
- Paste in the Notepad which automatically converts those into text format.
- Then select the cells where you want to paste those text format dates.
- Then click right and set them to Text.
- Then simply paste the data from Notepad.
Thus you can convert the date into a text string.
5. Using Text to Column Feature
We will try to solve the task by using the Text to Column wizard. Let me explain the steps one by one.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly go to the Text to Column option from the Data
- Check the option “Delimited” in the option and click Next.
- Then uncheck all the options from Delimited as I showed in the figure and click Next.
- Consequently check the Text option from the Column data format and preview the destination references.
- Finally, click the Finish option.
Thus, we converted the date into text format.
6. Using VBA Macro
We will use the VBA code in this method. The main idea behind this is to ease the process.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cells.
- Then go to view code by clicking right on the worksheet.
- Following that insert the following VBA code:
Sub Datetexts()
Dim c2 As Range
For Each c2 In Selection
c2.Value = Format(c2.Value, "dd.mm.yyyy")
Next c2
End Sub- And run the code.
- Finally, you will see the following results.
Using this method, you can transform a Date into a string of text in the (yyyymmdd) format.
📄 Important Notes
🖊️ Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.
🖊️ However, always look for ways that are easy to use in different situations.
🖊️ Every time you try to use shortcuts on the keyboard.
📝 Takeaways from This Article
📌 Use of IF and ISBLANK functions to convert a Date format to a text string of (yyyymmdd) format.
📌 Moreover, we will be able to convert a Date format to text format.
📌 In addition, we will be able to convert a Date format to text string of (yyyymmdd) format using VBA macros..
Conclusion
First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to excel convert date to text of (yyyymmdd) format. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook that I’ve shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website ExcelDen.com for further Excel-related articles.
Related Articles
- 3 Ways to Convert Number to Text with Commas in Excel
- How to Convert Number to Text with Leading Zeros in Excel
