How to Format Numbers, Dates, and Times in Excel With the TEXT Function
The TEXT function in Excel can be used to format numbers, dates, and times in a variety of ways. The syntax for the TEXT function is:
=TEXT(value, format_code)
- value is the cell reference or value that you want to format.
- format_code is a text string that specifies the format you want to use.
The format_code can be a combination of characters that represent the following:
- The type of data you want to format (number, date, or time).
- The number of decimal places or digits to display.
- The type of delimiters to use (such as commas, periods, or hyphens).
- The alignment of the text in the cell.
For example, the following formula would format the value in cell A1 as a date in the format “mm/dd/yyyy”:
=TEXT(A1, "mm/dd/yyyy")
The following table lists some of the most common format_codes that you can use with the TEXT function:
| Format_code | Description |
|---|---|
| dd | Day of the month as a number (01-31) |
| mm | Month as a number (01-12) |
| yyyy | Year as a four-digit number |
| m/d/yy | Month/day/two-digit year |
| d-mmm-yy | Day-month-two-digit year |
| dddd | Full day of the week name |
| mmm | Abbreviated month name |
| h:mm:ss AM/PM | Hour:minute:second in 12-hour AM/PM format |
| h:mm:ss | Hour:minute:second in 24-hour format |
You can find a complete list of format_codes in the Excel help documentation.
To use the TEXT function, you can enter the formula in the formula bar or in a cell. For example, to format the value in cell A1 as a date in the format “mm/dd/yyyy”, you would enter the following formula in the formula bar:
=TEXT(A1, "mm/dd/yyyy")
Once you have entered the formula, press Enter. The value in cell A1 will be formatted as a date in the specified format.
You can also use the TEXT function to combine text and numbers. For example, the following formula would display the current date and time in the cell:
=TEXT(NOW(), "dddd dd mmm, yyyy h:mm:ss AM/PM")
This formula would display the current date and time in the format “Wednesday 13 July, 2023 09:07:11 AM”.
The TEXT function is a powerful tool that can be used to format numbers, dates, and times in a variety of ways. By using the TEXT function, you can create custom formats that meet your specific needs.