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.
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.
The core of this formula is concatenation using the ampersand (&) operator: = "The date is" & date However, if you simply join a date with text and don't control the date format, the date will revert to it's raw serial number format,...
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 want to convert the date into a day name? If you want to convert the date value to a text value, you can use the TEXT...
Do you want to convert the date into a month name? If you want to convert the date value to a text value, you can use the TEXT function with a custom number format like "mmmm", or "mmm". In the example shown, the formula in cell C4 is: = TEXT ( B4...
The DATE function creates a valid date using three arguments: year, month, and day: = DATE ( year , month , day ) In cell C6, we use the LEFT, MID, and RIGHT functions to extract each of these components from a text string, and feed the results into...
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.