Convert date to text
Generic formula
=TEXT(date,format)
Related formulas
Join date and text
Get day name from date
Get month name from date
Convert text to date
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.