< 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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar