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.
|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.