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