In Excel, the date and time are stored as numbers. This allows the user to perform calculations with these dates and times. However, you might want these dates to behave like text at times. You’ll need to know how to convert a date to text in these situations. Assume you have a set of dates in mmddyyyy format that you wish to convert date to text yyyymmdd format, as seen in the screenshot below. In this article, I’ll show you how to rapidly convert date to text yyyymmdd format.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Easy Ways to Convert Date to Text YYYYMMDD
In this section, we will discuss three different methods to convert date to text yyyymmdd.
Method 1: Use TEXT Function to Convert Date to Text YYYYMMDD
The TEXT function converts a numeric number to a text string and displays it in the format you define.
Here’s an example of how dates and text can be blended. The dates lose their formatting and appear as digits in the merged text.
Let’s first see how the formula works for the TEXT Function,
The number you’d want to convert to text. This can be a number, a cell reference to a number, or a formula result with a number.
The format in which you prefer the number to be displayed Within double quotations, the format must be given.
Now we will apply this function to complete our task. Let’s follow these instructions below.
- Select a blank cell next to your date, for instance, C5.
- Type this following formula,
- Press Enter
- Then drag the AutoFill handle over the cells needed this formula.
Method 2: Apply Format Cells to Convert Date to Text YYYYMMDD
If you’re not a fan of Excel formulas, there is another cool way to quickly convert date to text in Excel – the Format Cells feature. Excel’s Format Cells function can quickly convert date to yyyymmdd format.
- Select the dates you want to convert to yyyy-mm-dd.
- Right-click to show the context.
- Choose Format Cells.
- In the Format Cells dialog, under the Number tab, select Custom from the Category list.
- Type yyyy-mm-dd into the textbox of type in the right section.
- Press Enter to see the results
Method 3: Use the TODAY Function to Convert Date to Text YYYYMMDD
If you wish to convert the current date to text, use the TEXT function in Excel in conjunction with the TODAY function, which returns the current date. The following formula describes how the function works,
- Select a blank cell.
- Type the formula. The formula is
✍ Things to Remember
✎ Make sure your values are formatted as Date. It will not change into yyyymmdd or any format until the value is in Date Format.
✎ The first clue that indicates a date formatted as text is the TEXT formula, which is oriented to the left. Aside from cell alignment, there are a few more signs in Excel that might help you distinguish between dates and text strings.
To conclude, I hope this article has given you some useful information about how to apply the Text function and Format Cell to convert date to text in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.
We, The Exceldemy Team, are always responsive to your queries.
Stay with us & keep learning.