Go Back

Convert date to text

Convert Date to Text in Excel

Formatting dates and turning them to text in excel the right way is important. Many people often make the mistake of directly turning dates in excel into text, losing the former.

But what you want to do is convert your date into text, without losing the dates in the columns.

Formula

= text(date,format)

How to convert date to text in Excel using this formula

In Excel, dates and times are usually represented as serial numbers. Then, they are converted to relatable values with numbers.

In 2 easy steps. You can convert dates to texts on Excel. This is assuming that you already have your columns filled with the dates you’re trying to convert.

Here’s how to:

  • Click on a cell beside the column where the date can be found.
  • For this cell, type the formula

Converting based on date formats

Different patterns can be used to represent your date formats in Excel. This includes structures like “dd/mm/yyyy”, “yyyy-mm-dd”, “yyyy-mm-dd”, etc. It is largely dependent on how you want to represent it on your spreadsheet.

Example:

Let us assume we want to convert a set of dates of a particular month to text formats. The table with the dates looks like this.

For test, enter a set of dates over a few rows like in the table below.

Fig 1. The column of dates to be converted to text.

In the cell next to your date, enter the formula for text conversion using the format “dd/mm/yyyy”.

Fig 2. Enter the formula in new cells.

You have something like this in the new cell.

Fig 3. Excel converts date to text using the formula.

You can repeat this process for other cells as well.

Example 2:

This time, we would use a different format in conversion – “ddd/mmm/yyyy”.

“ddd” returns the days in words – Mon, Tue, Wed, etc.

“mmm” here does not return the month in figures, rather it gives a shortened text as we know it, e,g. “Oct”, “Jan”, Aug

Do the same as the first example. Only difference is that now, with the formula =TEXT(A3, “ddd/mmm/yyyy”) your output looks like this:

Fig 4: Excel converts the date to text in “ddd/mmm/yyyy” format.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

How do I convert text to date format?How do I convert text to date format?How do I convert text to date format?How do I convert text to date format?
Solved by X. D. in 26 mins
How do I convert text to a date format?
Solved by M. D. in 13 mins
help me to convert text value into date and time value
Solved by G. U. in 29 mins

Leave a Comment

avatar