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:
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
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.
To join a date with text, you can use concatenation with the TEXT function to control the date format. In the example shown, the formula in E4 is: = "The date is " & TEXT ( B4 , "dddd, mmmm yyyy" ) How this formula works The...
If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs. Do you just want to display the day name? If you only want to display a day name, you don't need a formula – you can use a...
If you need to get the month name (i.e. January, February, March, etc.) from a date, you have several options depending on your needs. Do you just want to display the month name? If you only want to display a month name, you don't need a formula –...
To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT. In the example shown, the formula in C6 is: = DATE ( LEFT...
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.