Exceljet

Quick, clean, and to the point

Convert date to text

Excel formula: Convert date to text
Generic formula 
=TEXT(date,format)
Explanation 

If you need 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.

How this formula works

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. In a similar way, 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 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 TEXT to convert dates or any numeric value in a fixed 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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.