TedJordan.Org - Logo
Date Formatting in Excel with the Text Function

Table of Contents

Share this article

Join our newsletter

Excel: Date to Text (dd/mm/yyyy)

Convert a date to text in Excel within seconds with our free online Excel tutorial! Discover the handy TEXT function for dates.

How to convert a date to text (dd/mm/yyyy or in another format)? Many of you are wondering the same question so today, Ted Jordan teaches you how to do it with TEXT function.

The TEXT function for dates is mainly used to convert dates to texts or to show the name of the day of a date.

Today, we are converting dates to text values in the following formats: dd/mm/yyyy, dd/mm/yy and mm/dd/yyyy.

You’ll see: it’s quick and easy!
Are you ready for this free Excel tutorial?

Convert dates to text values in Excel by using the TEXT function. If you just change the format of a cell containing a date to text, using the Format tool, you will probably end up with a serial number in MS Excel.

Indeed, dates are registered as serial numbers in Excel!

Serial numbers are sequential numbers representing dates. By default, the serial number 1 corresponds to 1 January 1900. The serial number 1,000 will correspond to the 1,000th day after 1 January 1900: 26 September 1902.

Change a date to text in DD/MM/YYYY format by using the Excel formula =TEXT(value,”dd/mm/yyyy”).

value can be a cell reference with a date in the cell (B5 for example) or a date (“10/02/2024” for example).

Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/1/2023″,”dd/mm/yyyy”) will result in 12/01/2023 in text format.

In Excel, text is on the left of the cell while numbers or dates are on the right.

If you want to use another format than dd/mm/yyyy, check the examples below!

Convert a date to text as DD/MM/YY by using the Excel formula =TEXT(value,”dd/mm/yy”).

value can be a cell reference with a date in the cell (D5 for example) or a date (“14/2/2024” for example).

Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/01/2023″,”dd/mm/yy”) will result in 12/01/23 in text format.

In Excel, text appears on the left of the cell while numbers or dates are on the right.

If you are in the US, you may use MM/DD/YYYY format for dates. In this case, check the example below to change date to text as MM/DD/YYYY.

Change a date to text as MM/DD/YYYY by using the Excel formula =TEXT(value,”mm/dd/yyyy”).

value can be a cell reference with a date in the cell (D5 for example) or a date (“14/02/2024” for example).

Important: if you enter a date as the value argument, you have to insert it within quotation marks (“”). For example, =TEXT(“12/01/2023″,”mm/dd/yyyy”) will result in 01/12/2023 in text format.

In Excel, text is on the left of the cell while numbers or dates are on the right.

Now, you know how to convert dates to text values in Excel in different formats. If you need to extract a day from a date, use the same logic: the formula will be =TEXT(value,”dd”).

If you wish to change a date to a week day, as text, in Excel, check our tutorial about WEEKDAY. You will then be able to convert a date to days of the week, as per below.

A date with the corresponding day of the week next to it in Excel.
14/02/2024 is a Wednesday.