How to Use the TEXT Function to Extract Date Parts in Excel

The TEXT function is a useful tool in Excel that allows you to convert a number to text in a specified format. You can use the TEXT function to format numbers as dates, times, percentages, currencies, fractions, and more. In this article, we will focus on how to use the TEXT function to extract date parts, such as the year, month, day, weekday, or quarter from a date value.

A date in Excel is stored as a serial number that represents the number of days since January 1, 1900. For example, the date January 1, 2021 is stored as the number 44197. To display the date in a human-readable format, such as 01/01/2021, you need to apply a date format to the cell. You can do this by using the Format Cells dialog box (Ctrl+1) or the Number group on the Home tab.

However, sometimes you may want to extract a specific part of the date, such as the year, month, or day, and display it as text in another cell. For example, you may want to create a summary report that shows the sales by month and year, or you may want to group the dates by weekday or quarter. In these cases, you can use the TEXT function to convert the date value to text in the desired format.

The syntax of the TEXT function is:

=TEXT(value, format_text)

where value is the number or date that you want to convert to text, and format_text is a text string that specifies the number format to apply. The format_text argument must be enclosed in double quotation marks (“”). You can use any of the predefined or custom number formats that are available in Excel. For a list of common number formats, see this article.

Procedures

To use the TEXT function to extract date parts, follow these steps:

1. Enter the date value that you want to extract a part from in a cell, such as A2. Make sure the cell has a date format applied to it.
2. In another cell, enter the TEXT function with the date value as the first argument, and the desired date format as the second argument. For example, to extract the year from the date in A2, enter =TEXT(A2,”yyyy”). This will return the year as text, such as 2021.
3. To extract other date parts, such as the month, day, weekday, or quarter, use different date formats as the second argument of the TEXT function. For example, to extract the month name from the date in A2, enter =TEXT(A2,”mmmm”). This will return the month name as text, such as January. For a list of date format codes, see this article.
4. Copy and paste the TEXT function to other cells as needed, or use absolute references to lock the date value. For example, to extract the year from multiple dates in column A, enter =TEXT($A2,”yyyy”) and drag it down.

Explanation

The TEXT function converts a number or date to text in a specified format. The format_text argument determines how the value is displayed as text. You can use any of the number formats that are available in Excel, such as date, time, percentage, currency, fraction, scientific notation, etc. For example, you can use the TEXT function to format a number as currency with two decimal places and a thousand separator, like this:

=TEXT(1234.567,”$#,##0.00″)

This will return $1,234.57 as text.

To use the TEXT function to extract date parts, you need to use the appropriate date format codes as the format_text argument. For example, you can use the following date format codes to extract different date parts from a date value:

You can also combine different date format codes to create custom date formats. For example, you can use the following date format codes to create different date formats:

Note that the TEXT function always returns a text string, not a number or date. This means that you cannot use the result of the TEXT function in calculations or comparisons. For example, if you use the TEXT function to extract the year from a date, such as =TEXT(A2,”yyyy”), you cannot use the result to calculate the age, such as =TODAY()-TEXT(A2,”yyyy”). This will return an error, because you cannot subtract a text string from a date. To perform calculations or comparisons with dates, you need to use the original date value, not the TEXT function result.

Example

Suppose you have a list of sales transactions with dates in column A, and you want to create a summary report that shows the total sales by month and year. You can use the TEXT function to extract the month and year from the dates, and then use a pivot table to group and summarize the data. Here is an example of how to do this:

1. In column B, enter the TEXT function to extract the month name from the dates in column A, such as =TEXT(A2,”mmmm”). Copy and paste the formula to the rest of the column.
2. In column C, enter the TEXT function to extract the year from the dates in column A, such as =TEXT(A2,”yyyy”). Copy and paste the formula to the rest of the column.
3. Select the data range, including the headers, and insert a pivot table. You can use the Insert tab > PivotTable command, or press Alt+N+V.
4. In the PivotTable Fields pane, drag the Month field to the Rows area, the Year field to the Columns area, and the Sales field to the Values area. The pivot table will show the total sales by month and year, as shown below:

Other Approaches

The TEXT function is not the only way to extract date parts in Excel. You can also use other functions, such as the YEAR, MONTH, DAY, WEEKDAY, or QUARTER functions, to return the date parts as numbers. For example, you can use the YEAR function to extract the year from a date, such as =YEAR(A2). This will return the year as a number, such as 2021.

The advantage of using these functions is that they return numbers, not text, which means you can use them in calculations or comparisons. For example, you can use the YEAR function to calculate the age, such as =TODAY()-YEAR(A2). This will return the age as a number, such as 25.

The disadvantage of using these functions is that they do not allow you to format the date parts as text. For example, you cannot use the MONTH function to return the month name, such as January. You can only use the MONTH function to return the month number, such as 1. To display the month name, you need to apply a custom number format to the cell, such as mmmm.

Another approach to extract date parts in Excel is to use the DATEVALUE and TEXT functions together. The DATEVALUE function converts a text string that represents a date to a date value. The TEXT function converts a date value to text in a specified format. For example, you can use the DATEVALUE and TEXT functions to extract the year from a text string that represents a date, such as “01/01/2021”, like

=TEXT(DATEVALUE(“01/01/2021″),”yyyy”)

This will return 2021 as text.

The advantage of using the DATEVALUE and TEXT functions together is that they can handle different date formats as text inputs. For example, you can use the DATEVALUE and TEXT functions to extract the year from a text string that represents a date in the format DD-MMM-YY, such as “01-Jan-21”, like this:

=TEXT(DATEVALUE(“01-Jan-21″),”yyyy”)

This will return 2021 as text.

The disadvantage of using the DATEVALUE and TEXT functions together is that they require the text input to be a valid date format that Excel can recognize. For example, you cannot use the DATEVALUE and TEXT functions to extract the year from a text string that represents a date in the format YYYYMMDD, such as “20210101”, like this:

=TEXT(DATEVALUE(“20210101″),”yyyy”)

This will return an error, because Excel cannot convert “20210101” to a date value.