Convert date to text

An excel formula to convert date to text

Related Functions

Sponsored Link

 Convert date to text
=TEXT(date,format)

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.

Sponsored Link

0 votes. 0 / 5