The Excel TEXT function in Excel is a powerful tool that allows users to format numbers, dates, and times into customized, readable formats. When dealing with dates and times, it provides unmatched flexibility, enabling you to display data in ways that suit your specific needs.
This blog will walk you through the basics of the Excel TEXT function, its significance, and practical examples to help you master custom date and time formatting.
What Is the Excel TEXT Function?
The TEXT function converts a numeric value into text using a specified format.
Syntax:=TEXT(value, format_text)
- value: The date, time, or number to be formatted.
- format_text: A code that specifies how the value should be displayed.
Example: =TEXT(TODAY(), "dd-mm-yyyy") will display today’s date in the format 08-02-2025.
Why Use the TEXT Function for Dates and Times?
- Create customized formats tailored to your needs.
- Enhance readability by displaying data in user-friendly ways.
- Use dynamic text combined with dates for reporting purposes.
Common Custom Date and Time Formats
| Format Code | Description | Example Output |
|---|---|---|
d or dd | Day (single or double digits) | 8 or 08 |
ddd or dddd | Day name (short or full) | Sat or Saturday |
m or mm | Month (single or double digits) | 2 or 02 |
mmm or mmmm | Month name (short or full) | Feb or February |
yy or yyyy | Year (last two or all four digits) | 25 or 2025 |
h or hh | Hour (12-hour clock) | 3 or 03 |
mm (in time) | Minutes | 15 |
ss | Seconds | 45 |
AM/PM | Displays time in 12-hour format | 03:15 PM |
How to Use the TEXT Function for Custom Date and Time Formats
- Formatting Dates
Suppose the date in cell A1 is08-02-2025:
- Long Date:
=TEXT(A1, "dddd, mmmm dd, yyyy")
Output:Saturday, February 08, 2025 - Short Date:
=TEXT(A1, "mm/dd/yy")
Output:02/08/25 - Day and Month Only:
=TEXT(A1, "dd-mmm")
Output:08-Feb
- Combining Dates with Text
Create dynamic sentences or labels by merging text and dates:="Report generated on " & TEXT(A1, "dddd, mmmm dd, yyyy")
Output:Report generated on Saturday, February 08, 2025 - Formatting Time
If the time in cell B1 is15:30:45:
- 12-Hour Format:
=TEXT(B1, "hh:mm AM/PM")
Output:03:30 PM - Hours and Minutes Only:
=TEXT(B1, "hh:mm")
Output:15:30
- Formatting Date and Time Together
For a value like08-02-2025 15:30:45:=TEXT(A1, "dd-mmm-yyyy hh:mm AM/PM")
Output:08-Feb-2025 03:30 PM
Practical Applications
- Custom Headers: Use
=TEXT(TODAY(), "dddd, mmmm dd, yyyy")to display the current date in report headers. - Dynamic Dashboards: Combine
=TEXT(NOW(), "hh:mm:ss")with dashboards for live updates. - Invoices: Automate invoice issue dates using
=TEXT(TODAY(), "dd-mmm-yyyy"). - Event Tracking: Highlight important events like:
="Event scheduled for " & TEXT(A1, "dddd, mmmm dd")
Tips for Using the TEXT Function
- Always remember that TEXT converts values into text, making them unusable for further calculations.
- Pair it with functions like CONCATENATE or
&to create dynamic outputs. - Experiment with various format codes to find the best fit for your data.
Conclusion
The TEXT function is a must-have tool in your Excel skill set, especially when working with dates and times. Its ability to create custom formats adds professionalism and clarity to your spreadsheets, whether you’re building reports, creating dashboards, or automating tasks.
Start experimenting with the TEXT function today and see how it can transform your data into something meaningful and visually appealing!
If you found our contents helpful or enjoyable, consider supporting us with a small tip to help us create more valuable content!