Convert Date to Text in Excel: 5 Easy Steps to Readable Text

If you wanna know more about converting dates in your Excel sheet, you can read our guide here. Because many people find it hard to convert dates in a readable format. Yet, it doesn’t require any technical expertise.

Here we will look at ways for converting dates to text in Excel using easy-to-follow steps. We will also answer frequently asked questions to help you understand the concept better.

Introduction: how to convert date to text in excel

Excel is a popular spreadsheet program. Because users to perform various functions. These are including calculations, data analysis and formatting. One of the challenges tough is displaying dates in a readable text format.

When you enter a date in an Excel cell, it appears as a number by default. For instance, if you enter 01/01/2022 in a cell, it will appear as 44519. This is because Excel stores date as serial numbers. If you did not get it, here 1 representing January 1, 1900 actually.

convert excel date to text

Converting dates to text in Excel is essential if you work with company reports or monthly analysis stuff. For instance, if you are creating a report needing more readable format or if you are exporting data to other applications, they may not understand Excel date format.

how do i convert a date to text in excel

1. You will start with selecting the cell(s) with the date(s) you wanna convert.
2. Then, you will right-click on the cell(s) and select Format Cells from menu.
3. In the Format Cells box, you should select the Number tab.
4. In the Category list, you can select Text.
5. Now, you click OK to close the dialog box.

Converting dates to text in Excel is a simple process. And you can do it in two ways. These are using the TEXT function or by formatting the cells.

Using the TEXT Function to excel convert date to text

The TEXT function can convert any numeric value such as including dates to text. This is how to use the TEXT function to convert dates tough.

1. You will select the cell(s) to convert text.
2. In the formula bar, you should type =TEXT(A1,”dd/mm/yyyy”). Here, A1 is the cell is the date you want to convert to text.
3. And you can press Enter.

The formula will convert the date in the cell to text in the format dd/mm/yyyy. And dd represents the day, mm represents the month. While yyyy represents the year.

Formatting Cells to change date to text in excel

Formatting cells is another way to convert dates to text in Excel.

1. This time again you should select the cell(s) to convert to text.
2. Again, you will right-click on the cell(s) and select Format Cells.
3. In the Format Cells menu, you will select the Number tab.
4. Within Category list, this time you select Custom.
5. For Type field, you should enter dd/mm/yyyy.
6. Voila, you just click OK.

The cell(s) will now display the date in text format. dd/mm/yyyy.

Formatting Dates

Now you know how to convert dates to text in Excel. So,  it is time to explore how to format dates in Excel. Excel gives us formatting options for dates. Hence, we can customize the display of dates in your spreadsheets.

Custom Formatting formula to change date to text in excel

Custom formatting is useful to create your date format. Here are some examples of custom date formats to apply yourself.

• d/m/yyyy – This one displays the date as day/month/year. For example, 4/7/2023.
• dd-mmm-yyyy – It shows the date as day-month-year with the month in text format. Such as 04-Jul-2023.
• dddd, mmmm dd, yyyy – The date is the full day of the week, month, day, and year. Like Monday, July 4, 2023.

Why should you change date format to text format in excel

Converting dates to text in Excel has various benefits like below.

• Improved readability: When you convert excel date to text, you can make your Excel sheet more readable always.
• Compatibility to convert excel date to text: Converting dates to text can help ensure making compatible data with other applications. So you will not get errors.
• When you are converting dates to text, your data remains consistent and doesn’t change over time.

Frequently Asked Questions

Q1. Can you convert date to text in excel in bulk?

You can convert dates to text in bulk by selecting multiple cells with dates and following the steps outlined above.

Q2. How to change the date format after convert date to text in excel?

So, users can do it after converting to text. However, you first need to convert the text back to a date format before you can change the format.

Q3. Will converting dates to text affect calculations in Excel?

Yes, converting dates to text can affect calculations. Because Excel will not recognize those converted to text. And you need to convert them back to a format to perform calculations.

Q4. Is it Possible to convert text to dates in Excel?

You can convert text to dates in Excel using the DATEVALUE function.

Q5. How to display dates in a specific format?

You can display dates in a specific format if you use Format Cells dialog box and select your wanna be format.

Q6. How do I know if a cell contains a date or a number in Excel?

You can use the ISNUMBER and ISDATE functions to determine whether a cell contains a number or a date. The ISNUMBER function returns TRUE if the cell contains a number. And it will bring FALSE otherwise. While the ISDATE function returns TRUE if the cell has a date and FALSE otherwise.

Conclusion

In conclusion, converting in Excel is a helpful tool to improve the readability and compatibility of numbers you have. If you got the steps above, you can easily convert dates in Excel and avoid the common errors.

We hope this article has helped you understand the concept of converting text in Excel. If you have any other questions or need further assistance, don’t hesitate to contact us. Or, you can look for other guide articles to get better in Excel formulas.