Conversion Of Date To Text In Excel

Conversion Of Date To Text In Excel

Both date as well as time is stored in the form of numbers in Excel. This allows the user to utilize these dates as well as times for the purpose of calculations. One can add specified number of hours as well as days to any given date.


On the other hand, in some cases you may the dates in the form of text. Hence, it is essential to know how to carry out conversion of date to text.


Date can converted into text using Excel by following any one of the three ways:

  • Usage of Text function
  • Usage of text to column feature
  • Usage of the method of copy and paste


Two of the above mentioned methods have being discussed in detail in the following article:


  • Text Function: This function is used if the values must be displayed in specific format such as date format.


Conversion of Date to Text utilizing the Text Function

Text function can be used for displaying a value in a format desired. In the case considered, the function would be employed for displaying numerical date value into a text format.  


The syntax for the text function is as follows:

=TEXT (value, format_text)

Value as well as format text are the 2 arguments. Value refers to the number you desire to be converted in text. The format text refers to the format in which you desire the number to be displayed.


With respect to dates, the format has four parts:

  • Day format: The format for the same takes the form: d, dd, ddd, dddd

  • Month format: The format for the same takes the form: m, mm, mmm, mmmm

  • Year format: The format for the same takes the form: yy, yyyy

  • Separator: Forward slash, dash, space and comma can be used as separators


Using Text to column feature

Following steps can be followed to convert dates into the text format:

  • Select those cells which contain the dates which you desire to be converted into text format.

  • Follow the sequence: Data>Data Tools> Text to Column

  • In the wizard of Text to Column, make the below mentioned selections


Step 1: Ensure that Delimited is selected and select Next

Step 2: In the option named Delimiter, ensure that not even one of the options is checked.

Step 3: Click Text in the option mentioned “Column data format” and include the destination cell. After following this, click on the finish option.

This will lead to the conversion of dates into the format of texts.