How to Use TEXT Function for Date Format in Excel
Use the
TEXT function in Excel to convert a date into text with a specific format by writing =TEXT(date, "format"). Replace date with your date cell and "format" with the desired date pattern like "dd/mm/yyyy" or "mmmm dd, yyyy".Syntax
The TEXT function syntax is =TEXT(value, format_text).
- value: The date or number you want to format.
- format_text: A text string that defines the format you want to apply, enclosed in double quotes.
For dates, format_text uses date codes like dd for day, mm for month, and yyyy for year.
excel
=TEXT(A1, "dd/mm/yyyy")Example
This example converts the date in cell A1 to a text string formatted as "Month day, Year".
excel
=TEXT(A1, "mmmm dd, yyyy")Output
April 27, 2024
Common Pitfalls
One common mistake is forgetting to put the date format inside double quotes, which causes an error.
Another is using the wrong format codes, like MM instead of mm for months (uppercase MM means minutes).
Also, if the input is not a valid date, the function returns an error or unexpected text.
excel
=TEXT(A1, mm/dd/yyyy) <em>(wrong, missing quotes)</em> =TEXT(A1, "MM/dd/yyyy") <em>(wrong, MM is minutes)</em> =TEXT(A1, "mm/dd/yyyy") <em>(correct)</em>
Quick Reference
| Format Code | Meaning | Example Output |
|---|---|---|
| dd | Day with leading zero | 07 |
| d | Day without leading zero | 7 |
| mm | Month with leading zero | 04 |
| m | Month without leading zero | 4 |
| mmmm | Full month name | April |
| mmm | Short month name | Apr |
| yyyy | 4-digit year | 2024 |
| yy | 2-digit year | 24 |
Key Takeaways
Use =TEXT(date, "format") to convert dates to text with custom formats.
Always put the format pattern inside double quotes.
Use lowercase mm for months, uppercase MM means minutes.
Check that the input is a valid date to avoid errors.
Common date codes include dd, mm, yyyy, mmmm for flexible formatting.