Functions - “Text”

At one time or another, everyone needs to change a date into something else - a year, month, day of the week, or maybe time- related values, like hours or minutes. Many formulas you will find on the web discuss ways to “parse” a date; this approach treats the date as a string.  This can get tricky, since the date you see in Excel is NOT the date you are working with.  Indeed, you’re not dealing with a date at all; you’re dealing with a number. Excel stores dates as numbers.  For example, January 1, 2014 is stored as “41640”.  This represents the number of days from January 1, 1900.  Accordingly, January 1, 1900, is 1.  Times (like 12:15:47) are stored as decimal values to the right of the decimal point. So, if you try to use string manipulators, like RIGHT and LEFT, on a date, you will get rather odd results. Fortunately, Excel has anticipated this, and provides a very handy solution:  TEXT.  This function has two parameters; first, the input cell or expression, then the format you want: = TEXT ( expression,format ) The expression is most usually a cell.  For example, if cell A2 has a date in it (September 15, 2013, say), you could reference “A2” as the expression parameter. The format is the interesting parameter.  This can be any format that you might use in the “Custom Format” window of the Format cell command.  Here are some common options:
YYYY Four-digit year    “2013” YY Two-digit year              “13” MMMMM Single letter to denote the month          “S” MMMM Full month name  “September” MMM Three-character month name      “Sep” MM Month number with leading 0        “09” M Month number - No leading 0          “9” DDDD Full weekday name         “Sunday” DDD Three-character weekday name     “Sun” DD Day number with leading 0         “15” D Day number - No leading 0         “15”
For Hours, Minutes and Seconds, of course, you can use H, M and S, respectively. TEXT is not case-sensitive, so DDDD = dddd = DdDd, and so on. Finally, you can combine formats so, if you want to show December 13, 2014 as “2014-12-13”, you would use the format: “YYYY-MM-DD” Always remember to put your format into “” (double quotes). 
Home Home Why subscribe? Why subscribe? Training Training Purchase Purchase Links Links Blog Blog