Lesson#236: Excel TEXT Function: Mastering Date and Time Formatting
In the world of Excel, presenting dates and times in a readable and customized format is an essential skill. The Excel TEXT function is your secret weapon for achieving this. With the TEXT function, you can convert date and time values into various formats, from simple day-month-year representations to complex time intervals. In this expert-level guide, we’ll delve into the intricacies of the TEXT function, providing you with in-depth insights and a plethora of practical examples – ten in total – to become a formatting maestro in Excel.
Understanding the TEXT Function
Before we dive into practical examples, let’s grasp the fundamentals of the TEXT function:
=TEXT(value, format_text)value: The date or time value you want to format.format_text: A text string that defines the desired format for the value.
The TEXT function takes a date or time value and a format text string as arguments. It then returns the value in the specified format.
Now, let’s explore these functions in action with practical examples.
Examples of the TEXT Function for Date Formatting
Example 1: Basic Date Format
Suppose you have a date in cell A1 (e.g., “01/15/2023”) and want to display it as “15-Jan-2023.” You can use the TEXT function like this:
=TEXT(A1, "dd-mmm-yyyy")This formula converts the date into the desired format.
Example 2: Long Date Format
If you prefer the date to be displayed in a longer format like “January 15, 2023,” you can use the TEXT function like this:
=TEXT(A1, "mmmm dd, yyyy")This formula gives you a more verbose representation of the date.
Example 3: Custom Date Format
Let’s say you want to display the date in a custom format like “Jan-23.” You can use the TEXT function like this:
=TEXT(A1, "mmm-yy")This formula extracts the month and year from the date.
Example 4: Day of the Week
Suppose you have a date in cell A1 and want to display the day of the week (e.g., “Sunday”). You can use the TEXT function like this:
=TEXT(A1, "dddd")This formula retrieves the day of the week from the date.
Example 5: Ordinal Date Format
If you need a date format that includes ordinal indicators (e.g., “15th January 2023”), you can use the TEXT function like this:
=TEXT(A1, "dd""th"" mmmm yyyy")This formula adds “th” after the day.
Examples of the TEXT Function for Time Formatting
Example 6: Basic Time Format
Suppose you have a time in cell B1 (e.g., “14:30:00”) and want to display it as “02:30 PM.” You can use the TEXT function like this:
=TEXT(B1, "hh:mm AM/PM")This formula formats the time as desired, including AM/PM notation.
Example 7: 24-Hour Time Format
If you prefer a 24-hour time format (e.g., “14:30”), you can use the TEXT function like this:
=TEXT(B1, "hh:mm")This formula displays the time in a 24-hour clock.
Example 8: Custom Time Format
Let’s say you want to display the time in seconds but without AM/PM notation (e.g., “14:30:00”). You can use the TEXT function like this:
=TEXT(B1, "hh:mm:ss")This formula includes seconds in the time format.
Example 9: Hour and Minute Only
Suppose you have a time in cell B1 and want to extract only the hour and minute (e.g., “14:30”). You can use the TEXT function like this:
=TEXT(B1, "hh:mm")This formula removes seconds from the time.
Example 10: Elapsed Time
If you have a time duration in cell B1 (e.g., “03:15:30”) and want to display it as “3 hours, 15 minutes,” you can use the TEXT function like this:
=TEXT(B1, "[h] ""hours,"" m ""minutes""")This formula formats the time duration as desired.
Example 11: Military Time
=TEXT(B4, "HH:mm")This formula converts a time value in cell B4 (e.g., 09:00) to military time format as “09:00”.
Example 12: Time with Decimal Seconds
=TEXT(B5, "hh:mm.sss")This formula formats a time value in cell B5 (e.g., 14:45.789) with decimal seconds, resulting in “14:45.789”.
Example 13: Leading Zero for Hours
=TEXT(B6, "hh:mm")This formula ensures that a time value in cell B6 (e.g., 8:15) displays with a leading zero for hours as “08:15”.
Examples of Combined Date and Time Formatting
Example 14: Date and Time in ISO Format
=TEXT(C1, "YYYY-MM-DDThh:mm:ss")This formula formats a datetime value in cell C1 (e.g., 2023-08-31 16:45:00) as “2023-08-31T16:45:00”.
Example 15: Short Date with Time
=TEXT(C2, "MM/DD/YYYY hh:mm AM/PM")This formula combines date and time values from cell C2 (e.g., 09/15/2024 12:30) into “09/15/2024 12:30 PM”.
Example 16: Date in Parentheses with Short Time
=TEXT(C3, "(DD-MMM-YYYY) hh:mm")This formula formats a DateTime value in cell C3 (e.g., 12-Sep-2025 18:15) as “(12-Sep-2025) 18:15”.
Example 17: Time with Milliseconds
=TEXT(C4, "hh:mm:ss.000")This formula includes milliseconds in a time value from cell C4 (e.g., 09:30:45.789) as “09:30:45.789”.
Example 18: Date with Weekday and Time
=TEXT(C5, "dddd, DD-MMM-YYYY hh:mm AM/PM")This formula combines the full weekday, date, and time from cell C5 (e.g., Tuesday, 23-Nov-2023 14:00) into “Tuesday, 23-Nov-2023 02:00 PM”.
Example 19: Display as “Time Ago”
=TEXT(NOW() - C6, "h 'hours and' m 'minutes ago'")This formula calculates and displays the time elapsed since a datetime value in cell C6 (e.g., 2023-08-31 10:30:00) as “2 hours and 15 minutes ago”.
Advanced Examples
Example 20: Conditional Date Formatting
=IF(A1>TODAY(), "In the future: " & TEXT(A1, "DD-MMM-YYYY"), "In the past: " & TEXT(A1, "DD-MMM-YYYY"))This formula evaluates whether a date in cell A1 is in the future or past and formats it accordingly.
Example 21: Concatenate Date and Time
=TEXT(DATE(2023, 12, 31) + TIME(15, 30, 0), "DD-MMM-YYYY hh:mm AM/PM")This formula combines a specific date and time (e.g., December 31, 2023, 3:30 PM) and formats it.
Example 22: Display Age
=INT((TODAY() - A1) / 365) & " years"This formula calculates a person’s age based on their birthdate in cell A1 and displays it with “years”.
Example 23: Custom Week Number
=TEXT(A1, "ww") & " Week of " & TEXT(A1, "YYYY")This formula extracts the week number and year from a date in cell A1 and formats it as “35 Week of 2023”.
Example 24: Conditional Time Formatting
=IF(B1<TIME(12,0,0), TEXT(B1, "hh:mm AM/PM"), TEXT(B1, "hh:mm:ss AM/PM"))This formula checks if a time in cell B1 is before noon and formats it accordingly.
Example 25: Displaying Quarter
= "Q" & INT((MONTH(A1)-1)/3)+1This formula calculates the quarter of a date in cell A1 and displays it as “Q1”, “Q2”, etc.
Example 26: Custom Time Format with Milliseconds
=TEXT(B2, "hh:mm:ss.000")This formula formats a time value in cell B2 (e.g., 15:45:30.123) with milliseconds.
Practical Real-Life Examples
Example 27: Invoice Date
="Invoice Date: " & TEXT(A1, "MMMM DD, YYYY")This formula creates an invoice date format for a date value in cell A1.
Example 28: Project Deadline
="Deadline: " & TEXT(C1, "dddd, DD-MMM-YYYY hh:mm AM/PM")This formula formats a project deadline with the day, date, and time from cell C1.
Example 29: Event Schedule
="Join us on " & TEXT(A2, "dddd, DD-MMM-YYYY") & " at " & TEXT(B2, "hh:mm AM/PM")This formula combines date and time values to create an event schedule.
Example 30: International Date Format
=TEXT(A3, "YYYY-MM-DD")This formula converts a date in cell A3 to an international date format (e.g., 2023-09-15).
Conclusion
The Excel TEXT function is a powerful tool for formatting dates and times to meet your specific needs. By understanding its syntax and exploring these practical examples, you can gain full control over how you present date and time values in your spreadsheets. Whether you need a simple date format or a complex time duration representation, the TEXT function is your key to achieving precise formatting. It empowers you to create more readable and visually appealing reports, dashboards, and data presentations in Excel.
Stay updated with Excelabcd. Join our WhatsApp channel.
Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.
Leave a Reply