Date to Text in Excel

Table of Contents

Date to Text in Excel
How to Convert Date to Text in Excel?

Convert Date to Text in Excel

Generally, Date in Excel is stored as numbers. This allows a user to use these dates in calculations in excel. For example, you can add any number of days to a given date.

However, sometimes the date is required as text. You need to convert the date into the text format.

How to Convert Date to Text in Excel?

The conversion of the date to text is very easy and simple.

There are three simple ways to convert date to text in Excel:

Using the Text Function (most commonly used)
Using the Text to Column option
Using Copy Paste method

Use of Date to Text in Excel worksheet.

In Excel Text function 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 formula of the Excel TEXT function is as follows:

Value: It is a numeric value you want to convert to text. This 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 date to text in excel 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 – for day number.
dd –For day number with a leading zero.
ddd – For Wednesday, it will show Wed.
dddd –For Tuesday, it will be shown as Tuesday.

Month Format:

m –for month number
mm – for month number with a leading zero
mmm –For August, it will show Aug.
mmmm For August, it will show complete August.

Year Format:

yy –For 2018, it will show 18.
yyyy –For 2018, it will show 2018.

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-11-2018.
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 Jul, 2018.

Example #1 – Text Function in Excel

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

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

To get the below output.

Example #2 – Covert Date using Text Function

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

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

To get the below output.

Example #3 – Convert Date to Text in Excel using Text to Column

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

Select the data and Go to Data then Data Tools and Text to Column.

Press Next and uncheck all Delimiters

Select the location and click on the finish as shown in the below figure.

The output will be as follows:

Example #4 – Convert Date to Text using the Copy-Paste Method

Consider the data set of joining date

Copy it and paste it in the notepad Open a Notepad and paste it there. As soon as you paste the dates in the notepad, it automatically gets converted into text.

Now switch back to the Excel and select the cells where you want to paste these dates.

With the cells selected, go to Home then Number and select the Text format (from the drop down).

Paste the date as text

Convert Date to Text using VBA.

Suppose we have the text in the Sheet1 and in the H2 cell then apply the Date to text by using the below VBA code.

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 about the Date to Text in Excel:

Left-aligned by default
If several text dates are selected, the Status Bar only shows Count.
There may be a leading apostrophe visible in the formula bar.

You can download this Date to Text in Excel template here – Convert Date to Text Excel Template