MY CARTVideo Lesson:
This video lesson, titled “How to Use the TEXT Function in Excel to Format Dates,” shows you how to use the TEXT function in Excel to format dates.
How to Use the TEXT Function in Excel to Format Dates:
The TEXT function in Excel lets you convert dates and numbers to text and specify their format. The real world example we’ll look at in this video shows you how to convert a date/time number to text and specify its date formatting by using the TEXT function in Excel. It also explains why you may need to do that. This example shows order data from a query exported from the Northwind sample database in Access.
After opening the exported data in Excel, it shows each order’s date in the “OrderDate” column. To analyze the sales by date, we’ve added a criteria range to the side of the data that uses a data validation drop-down, which lets the user select a date value from the “OrderDate” column. Next to that, we’ve added a formula cell containing the SUMIF function that sums the “OrderTotal” column of a data record if its “OrderDate” value matches the selection from the data validation drop-down.
However, if a user selects a date from the data validation drop-down, the formula shows incorrect order total information. This is because while the “OrderDate” field displays the date value, it actually contains a more specific date/time value, which you can see by clicking on any date in the “OrderDate” field and then looking in the Formula Bar. So, the order dates are not being grouped together correctly for the formula, as they have different date/times associated with them.
How to Fix This Using the TEXT Function:
One way to fix this is to replace the date/time value in the “OrderDate” field with the text value of its date only by using the TEXT function in a new formula. Doing this then allows the rows to be grouped together correctly and show the order totals by date. To use the TEXT function, click into a cell where you want to show the results. Then type:
=TEXT(
Then type or select the cell containing the date/time value to convert, followed by a comma. Next, type the desired date format to apply to the cell. For this example, we’ll use the standard 2 digit month, two digit day, and 4 digit year format, separated by forward slashes. So, type:
“mm/dd/yyyy”
Finally, close the parenthesis and then press the “Enter” key on your keyboard. Then copy or fill the formula down the column to convert the values. Going back to our example, we would then copy the selected cells and use paste special to paste only the values over the existing values in the “OrderDate”column. This then replaces the date/time values with the new formatted text values, allowing the existing data validation drop-down and SUMIF function formula to work correctly to show order totals by date.
More Information:
If you want to learn more about the format codes used by the TEXT function in Excel, you can easily see more commonly used format codes by looking within the “Custom” format category on the “Number” tab of the “Format Cells” dialog box. Alternatively, Microsoft also lists additional types of formats you can use on the help page for the TEXT function in Excel.
