FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Date to Text in Excel

Updated on December 27, 2023
Article byTanuj Kumar
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

How to Convert Date to Text in Excel?

The conversion of the date to text is very easy and simple. There are four simple ways to convert dates to text in Excel:

  1. Using the TEXT function (most commonly used)
  2. Using the TEXT to COLUMN option
  3. Using the copy-paste method
  4. Using VBA

Use of Date to Text in Worksheet

Excel Text functionExcel Text FunctionTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more is commonly used to convert a numeric value to a text string and display it in the format you specify so we can use it to convert date to text in Excel format.

Date to Text Function in Excel

The TEXT formula in excelTEXT Formula In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more is as follows:

Text Formula in Excel
  • Value: It is a numeric value you want to convert to text. It can be a number, a formula that returns a numeric value, or a reference to a cell containing a number.
  • format_text: This is how you want to format the resulting text value, provided as a text string enclosed in quotation marks.

Using the TEXT function to convert dates to text in Excel requires a basic understanding of the formats we can use.

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

  • day format
  • month format
  • year format
  • separator
You can download this Convert Date to Text Excel Template here – Convert Date to Text Excel Template

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#1 Using Text Function

Example #1

Apply the formula =A4&”‘s joining date is “&TEXT(B4,”dd-mm-yyyy”)

Date to Text in Excel Example 1

To get the below output.

Date to Text in Excel Example 1-1

Example #2

You can also convert the current date to text by the below formula:

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

Date to Text in Excel Example 2

To get the below output.

Date to Text in Excel Example 2-1

#2 Using Text to Column

Suppose we have a dataset, as shown below, and we want to convert these dates into textConvert These Dates Into TextTo convert a date to text in Excel, right-click on the date cell and select the format cells option. A new window will open. You can convert the date to text by selecting the desired format from a list of options.read more format:

Date to Text in Excel Example 3

Below are the steps to convert the date into text:

  1. First, we must select the data and go to “Data,” then “Data Tools,” and Text to Column in Excel.


    date to text in excel example 3-1

  2. Press “Next” and uncheck all “Delimiters.”


    date to text in excel example 3-2

  3. Then, we must select the location and click on the “Finish,” as shown below.


    date to text in excel example 3-3

    The output will be as follows:

    date to text in excel example 3-4

#3 Using Copy Paste Method

Consider the data set of the joining date.

date to text in excel example 4
  • First, copy the data. Then, open a notepad and paste it there. As soon as you paste the dates into the notepad, it automatically gets converted into text.
  • Now switch back to Excel and select the cells where you want to paste these dates.
  • Then, we must go to “Home” then “Number” with the cells selected and select the “Text” format (from the drop-down).
  • Finally, paste the date as text.
date to text in excel example 4-1
date to text in excel example 4-2

#4 Using VBA

Suppose we have the text in sheet1 and the H2 cell, then apply the date to the text using the below VBA codeUsing The Below VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Sub Datetotexts ()

Dim c As Range

For Each c In Selection

c.Value = Format(c.Value, “dd.mm.yyyy”)

Next c

End Sub

Things to Remember

This article is a guide on How To Convert Date to Text in Excel. Here, we discuss the top 3 methods to convert the date to text: 1) Text Formula, 2) Text to Column, and 3) Copy Paste Method, along with examples and a downloadable Excel template. You may also look at these useful functions in Excel: –