Explanation
The TEXT function applies the number format you specify to a numeric value, and returns a result as text.
In this case, the number format provided is “yyyymm”, which joins a 4-digit year with a 2-digit month value.
Display only option
If you only want to display a date with the year and month, you can simply apply the custom number format “yyyymm” to the date(s). This will cause Excel to display the year and month together, but will not change the underlying date.
Explanation
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. Similarly, 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 a 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 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 .
