Excel's TEXT function and some other, formula-free methods for converting dates to text
Several methods for doing this in Excel were covered in the prior article. If you need to do the inverse, convert a date from Excel to text, you have a similar selection of options.
As is customary, we'll start with a formulaic answer and then consider some alternative approaches.
Excel's TEXT function was developed to help you display numerical data in the format of your choosing as a string of text.
Excel's TEXT function has the following syntax:
Value in TEXT(format_text)
- value is a number that needs to be written out. A cell reference, a number, or a formula that returns a number are all acceptable options.
- format_text This is the desired format for the resulting text value, and it should be supplied as a string of text within quotation marks.
The following formula, for instance, will take a date entered in cell A1 and output it as a string of text in the common US date format (month/day/year):
The first telltale sign that you're dealing with a textual representation of a date is that the value returned by the TEXT formula is aligned to the left, as shown in the preceding screenshot. Excel provides a number of visual cues beyond cell alignment that allow you to tell the difference between dates and text strings.
The TEXT function in Excel has no trouble converting dates to text values because dates are serial numbers. The most difficult part is likely to be choosing the correct date format for display in the text.
The following date formats are recognized by Microsoft Excel:
- m = month number, excluding January
- Zero-based month indicator (mm)
- mmm is an abbreviation for the full name of a month, such as "March."
- mmmm - abbreviation for "month, day, and year," in this case "March"
- mmmmm - month initial, as in M (representing March and May).
- d = the number of days, excluding the first zero
- dd is shorthand for "day of the year"
- day-of-week abbreviation (ddd): Sunday, for instance
- full name of the day of the week, such as "Sunday" in the format dddd
- year, two digits (yy)
- yyyy - year with four digits
You can customize the appearance of the converted text date by using different delimiters like a dash (-), slash (/), comma (,), colon (:), etc. to separate the date codes. Some instances are as follows:
- U.S. date format "mm/dd/yyyy" shows as 03/08/2015.
- The internationally standard date format, "dd/mm/yyyy," shows 08/03/2015.
- To avoid any confusion, the format "dd-mmm-yy" is displayed as "08-Mar-15."
- Date formatted as "dddd, mmmm d, yyyy," showing the entire date, including the day of the week, as Sunday, March 08, 2015
So, if you want to export a column of US dates from Excel to a As a courtesy to your UK-based business partner, you can change the date formats in your.csv file by following these steps:
The following are some additional examples of formulas and the results they produce:
You've used the following time codes in the format_text argument of the Excel TEXT function if your date entries also show times and you want to convert them to text strings without any formatting changes.
- hours (h) 0-23 (no leading zero)
- hh - hours that start with a zero, like 00-23
- m - minutes, as in 0–59 with no leading zero.
- minutes (mm): minutes followed by a zero, as 00:59.
- s = seconds, excluding the initial zero
- ss - zero-based seconds
Daily Time Frames:
- Time of Day Displays as AM or PM
- When no other time format is given, 24 hour time is assumed.
You may be wondering how Microsoft Excel tells the difference between the m codes for months and the m codes for minutes. You can tell Excel to show minutes instead of months by placing an "m" after h codes (hours) or before s codes (seconds). That's right, it's that easy:)
Examples of using Excel's TEXT function with its format_text argument include:
For example: =TEXT(A2,"dd/mm/yyyy hh:mm")
To convert just the time information, enter the time codes only, as shown here:
A2="h:mm AM/PM" =TEXT
Your TEXT formulas' output might look something like this:
In Excel, you can use the TEXT function to convert the current date to text format by combining it with the TODAY function, which returns the current date.
Using =TEXT(TODAY(), "dd-mmm-yyyy")
The date of March 8, 2015 would be the answer to this equation. See the date codes discussed in Example 1 if you'd like to present the final string of text in a different format.
Though Excel's TEXT function is primarily meant to transform numerical data into text, it can also do the inverse. e edit for current date The TEXT formula only requires the addition of the double negation (--) to accomplish this.
Using the below formula, you can change the format of cell A1 so that it reads like a date, for instance.
Note For the purposes of these examples, we've been using Excel's English locale's date and time formats. It's possible that the language codes are different depending on your location.
You saw how the TEXT function in Excel converts dates to text effectively. However, if you're not a fan of Excel formulas, this alternative may be more to your liking.
If you read the first installment of our Excel dates tutorial, you know how to convert text to dates with the help of the Text to Columns feature. The only difference between this and the date conversion process is the final step of the wizard, where you'll select Text rather than Date.
Note No matter how the dates are displayed in your worksheet, the Text to Column wizard will always convert them to the default short date format. The following article, Default date format in Excel, provides more information on Excel's predefined date and time formats.
You can skip ahead to the next solution that allows you to convert dates to text strings in any format of your choosing if the standard date format is not what you need.
Follow these steps if you don't mind using the standard layout:
- To convert multiple dates to text in Excel, select the cells containing the dates to be edited.
- Click Text to Columns in the Data Tools sub-group of the Data tab.
- In the first screen of the wizard, choose the "Delimited" format.
- Click Next on Step 2 of the Wizard without checking any delimiter boxes.
- Select Text under Column data format on the third and final step of the wizard, then press Finish.
Tip Choosing a different Destination for the new column's top cell will prevent the generated text strings from replacing the original dates.
Wow, that was simple, right? You can see the outcome in the screenshot below, which shows dates converted to text strings using the short date format determined by your Windows Regional settings (in my case, "mm/dd/yyyy").
Using Notepad or another text editor is another quick, formula-free option for converting Excel date ranges to text strings. Excel date can be converted to text in any format you like with this tool, unlike the Text to Columns wizard.
- You can format the dates in Excel to match the desired appearance of the text strings.
- To convert multiple dates at once, simply check the boxes next to them. Ctrl C to imitate them
- To save the copied dates, open Notepad or another text editor and click the "Paste" button.
- Dates are converted to text format automatically in Notepad. Press Ctrl A to pick out all of the words, and then Ctrl C imitate them
- Returning to Excel, pick the column into which you'll be dropping the text strings, and formatting it as Text will do the trick. Please use the Ctrl 1 to bring up the Format Cells dialogue, click the Numbers tab, and then choose Text.
- Once you've decided on the first cell to insert the text strings into, you can press Ctrl V stick them
Column B contains the original Excel dates, while column D contains the text entries. Note that the converted text strings are left-highlighted, as should be the case with all text values in Excel, and otherwise accurately reflect the original date format.
Follow these steps to transform Excel dates into text format: We will examine some Excel functions for dealing with weekdays and yearly calendar days next week. And while you wait, you might want to review the earlier sections of our in-depth guide to manipulating dates and times in Excel.
If you're thinking about taking a trip to the United States, you might consider exchanging some of your money into U.S. dollars, which is the official currency of the country. The international symbol for the currency is USD.USD is also the official currency in a few other countries, including Ecuador
If you're considering a journey to the United States, it might be beneficial to convert some of your money into U.S. dollars, which is the official currency of the country. The internationally recognized symbol for this currency is USD.Additionally, USD serves as the official currency in Ecuador and El
Utilize our inch-to-fraction calculator to effortlessly perform conversions between inch fractions, decimal values, metric measurements, and feet. Effective Techniques for Calculating Inch FractionsInches can be represented as fractions or decimals. When dealing with inch fractions, it is vital to
Please enter the necessary values below to convert kilowatts [kW] to British thermal units per hour [Btu/h], or the other way around.Description: A kilowatt (symbol: kW) is a unit of power within the International System of Units (SI). The watt, after the Scottish inventor James Watt, serves as the base