Convert date to text

To convert dates to text (i.e. date to string conversion), you can use the TEXT function. The TEXT function can use patterns like "dd/mm/yyyy", "yyyy-mm-dd", etc. to convert a valid date to a text value. See table below for a list of available tokens.

Explanation

Dates and times in Excel are stored as serial numbers and converted to human-readable values on the fly using number formats. When you enter a date in Excel, you can apply a number format to display that date as you like. Similarly, the TEXT function allows you to convert a date or time into text in a preferred format. For example, if the date January 9, 2000, is entered in cell A1, you can use TEXT to convert this date into the following text strings as follows:

=TEXT(A1,"mmm")            // "Jan"
=TEXT(A1,"dd/mm/yyyy")     // "09/01/2012"
=TEXT(A1,"dd-mmm-yy")      // "09-Jan-12"

Date format codes

Assuming a date of January 9, 2012, here is a more complete set of formatting codes for a date, along with sample output.

Format code	Output
d	9
dd	09
ddd	Mon
dddd	Monday
m	1
mm	01
mmm	Jan
mmmm	January
mmmmm	J
yy	12
yyyy	2012
mm/dd/yyyy	01/09/2012
m/d/y	1/9/12
ddd, mmm d	Mon, Jan 9
mm/dd/yyyy h:mm AM/PM	01/09/2012 5:15 PM
dd/mm/yyyy hh:mm:ss	09/01/2012 17:15:00

You can use the TEXT function to convert dates or any numeric value to a fixed text format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left. Also, see Excel custom number formats.