Convert Date to Text in Excel – Explained with Examples

Convert Date to Text – Video

Date and Time in Excel are stored as numbers. This enables a user to use these dates and time in calculations. For example, you can add a specific number of days or hours to a given date.

However, sometimes you may want these dates to behave as text. In such cases, you need to know how to convert the date to text.

Below is an example where dates are combined with text. You can see that the dates don’t retain their format and show up as numbers in the combined text.

Convert Date to Text - Problem Dataset

In such situations, its required to convert a date into text.

Convert Date to Text in Excel

In this tutorial, you’ll learn three ways to convert the date to text in Excel:

  • Using the Text Function
  • Using the Text to Column feature
  • Using Copy Paste method

Convert Date to Text using Text Function

TEXT function is best used when you want to display a value in a specific format. In this case, it would be to display the date (which is a number) in the date format.

Let’s first see how the text function works.

Here is the syntax:

=TEXT(value, format_text)

It takes two arguments:

  • value –  the number that you want to convert into text. This can be a number, a cell reference that contains a number, or a formula result that returns a number.
  • format_text – the format in which you want to display the number. The format needs to be specified within double quotes.

Using the Text function requires a basic understanding of the formats that you can use in it.

In the case of dates, there are four parts to the format:

  • day format
  • month format
  • year format
  • separator

Here are the formats you can use for each part:

  • Day Format:
    • d – it shows the day number without a leading zero. So 2 will be shown as 2 and 12 will be shown as 12.
    • dd – it shows the day number with a leading zero. So 2 will be shown as 02, and 12 will be shown as 12
    • ddd – it shows the day name as a three letter abbreviation of the day. For example, if the day is a Monday, it will show Mon.
    • dddd – it shows the full name of the day. For example, if it’s Monday, it will be shown as Monday.
  • Month Format:
    • m – it shows the month number without a leading zero. So 2 will be shown as 2 and 12 will be shown as 12.
    • mm – it shows the month number with a leading zero. So 2 will be shown as 02, and 12 will be shown as 12
    • mmm – it shows the month name as a three letter abbreviation of the day. For example, if the month is August, it will show Aug.
    • mmmm – it shows the full name of the month. For example, if the month is August, it will show August.
  • Year Format:
    • yy – it shows the two digit year number. For example, if it is 2016, it will show 16.
    • yyyy – it shows the four digit year number. For example, if it is 2016, it will show 2016.
  • Separator:
    • / (forward slash): A forward slash can be used to separate the day, month, and year part of a date. For example, if you specify “dd/mmm/yyyy” as the format, it would return a date with the following format: 31/12/2016.
    • – (dash): A dash can be used to separate the day, month, and year part of a date. For example, if you specify “dd-mmm-yyyy” as the format, it would return a date with the following format: 31-12-2016.
    • Space and comma: You can also combine space and comma to create a format such as “dd mmm, yyyy”. This would show the date in the following format: 31 Mar, 2016.

Let’s see a few examples of how to use the TEXT function to convert date to text in Excel.

Example 1: Converting a Specified Date to Text

Let’s again take the example of the date of joining:

Convert Date to Text - Problem Dataset

Here’s the formula that will give you the right result:

=A2&”‘s joining date is “&TEXT(B2,”dd-mm-yyyy”)

Convert Date to Text - Joining date example

Note that instead of using the cell reference that has the date, we have used the TEXT function to convert it into text using the specified format.

Below are some variations of different formats that you can use:

Convert Date to Text - sample formats

You can experiment with other formats as well and create your own combinations.

Example 2: Converting Current Date to Text

To convert the current date into text, you can use the TODAY function along with the TEXT function.

Here is a formula that will do it:

=”Today is “&TEXT(TODAY(),”dd/mm/yyyy”)

This could be useful in dashboards/reports, where as soon as the file is opened (or any changes are made), the date refreshes to show the current date.

Convert Date to Text - current date

Convert Date to Text using Text to Column

If you’re not a fan for Excel formulas, there is another cool way to quickly convert date to text in Excel – the Text to Column feature.

Suppose you have a dataset as shown below and you want to convert these dates into text format:

Convert Date to Text - text to column 1a

Here are the steps to do this:

  • Select all the cells that contain dates that you want to convert to text.
  • Go to Data –> Data Tools –> Text to Column.Convert Date to Text - text to column 2
  • In the Text to Column Wizard, make the following selections:
    • Step 1 of 3: Make sure Delimited is selected and click on Next.Convert Date to Text - text to column 3
    • Step 2 of 3: In the delimiter options, make sure none of the options is selected. Deselect the one which is selected by default and click on Next.Convert Date to Text - text to column 4
    • Step 3 of 3: Select Text in the ‘Column data format’ options and specify the destination cell (B2 in this example) and click on Finish.Convert Date to Text - text to column 5

This would instantly convert the dates into text format.

Convert Date to Text - text to column 6

NOTE: There is a difference in format of the dates in the two columns. While the original format had dd mmm, yyyy format, the result is dd-mm-yyyy. Remember that the Text to Column feature will always convert dates to the default short date format (which is dd-mm-yyyy as per my systems regional settings. It could be different for yours).

If you want the date in other formats, use the formula method, or the copy-paste method shown below.

Convert Date to Text using the Copy-Paste Method

This is the fastest way to convert date to text.

Here are the steps:

  • Select the cells that have dates that you want to convert and copy it.Convert Date to Text - copy paste 4
  • Open a Notepad and paste it there. As soon as you paste the dates in the notepad, it automatically gets converted into text.Convert Date to Text - copy paste 2
  • Now switch back to the Excel and select the cells where you want to paste these dates.
  • With the cells selected, go to Home –> Number and select the Text format (from the drop down).Convert Date to Text - copy paste 3
  • Now go back to the notepad, copy the dates and paste it in Excel. You’ll see that the dates have been converted into text.

Convert Date to Text - copy paste 5

You May Also Like the Following Excel Tutorials:

>