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)

Where:

  • 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):

=TEXT(A1,"mm/dd/yyyy")
Using Excel TEXT function to convert a date to a text string

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:

Months:

  • 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).

Days:

  • 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

Years:

  • 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:

=TEXT(A1,"mm/dd/yyyy")

The following are some additional examples of formulas and the results they produce:
Converting dates to text format in Excel using the TEXT function

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:

  • hours (h) 0-23 (no leading zero)
  • hh - hours that start with a zero, like 00-23

Minutes:

  • m - minutes, as in 0–59 with no leading zero.
  • minutes (mm): minutes followed by a zero, as 00:59.

Seconds:

  • 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:
Text formulas to convert date and time to text strings

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.

=--TEXT(A1,"mm/dd/yy")
A TEXT formula to convert text to date

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:

  1. To convert multiple dates to text in Excel, select the cells containing the dates to be edited.
  2. Click Text to Columns in the Data Tools sub-group of the Data tab.
    Switch to the Data tab and click Text to Columns.
  3. In the first screen of the wizard, choose the "Delimited" format.
    On step 1 of the wizard, select Delimited and click Next.
  4. Click Next on Step 2 of the Wizard without checking any delimiter boxes.
    On step 2 of the wizard, uncheck all delimiter boxes and click Next.
  5. 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.
Converting dates to text format using the Convert Text to Columns Wizard

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").
Dates converted to text strings in the default short date format

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.

  1. You can format the dates in Excel to match the desired appearance of the text strings.
  2. To convert multiple dates at once, simply check the boxes next to them. Ctrl C to imitate them
    Copy the dates you want to convert to text.
  3. To save the copied dates, open Notepad or another text editor and click the "Paste" button.
  4. 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
  5. 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.
    Select the column where you want to insert the text strings and apply the Text format to it.
  6. 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.
The converted text strings look identical to the original dates.

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.

"Transform Your Gaming Experience with the Top 10 Wii to HDMI Adapters of 2023: A Detailed Reviews and Buying Guide"
"Transform Your Gaming Experience with the Top 10 Wii to HDMI Adapters of 2023: A Detailed Reviews and Buying Guide"

Numerous individuals possess a Wii console but are unable to use it due to improper cables or incompatibility with their TV or monitor. However, you can effortlessly connect a Wii console to any TV utilizing an adapter since all modern TVs and monitors support HDMI connections. Thus, we have

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 01:43:59
"Upgrade Your Gaming System Now with the Top 5 HDMI Adapters for Wii [2023 Edition]"
"Upgrade Your Gaming System Now with the Top 5 HDMI Adapters for Wii [2023 Edition]"

Do you remember how the Nintendo Wii revolutionized gaming? Its motion controls were a game-changer and the available games, such as Wii Sports, were amazing. But as technology progressed, the Wii became outdated, and playing it nowadays is challenging because of video output compatibility

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 00:13:36
A Comprehensive Guide to Using TerraMatch
A Comprehensive Guide to Using TerraMatch

The Convert Time Stamps feature can be employed to transform the format of time stamps. A range of conversions are supported, including GPS seconds-of-week to GPS standard time, GPS time to GPS standard time, GPS seconds-of-day to GPS seconds-of-week, Unix time to GPS standard time, UTC seconds-of-day

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-02 00:12:09
Unlock the Secret to Transferring Gift Cards to PayPal!
Unlock the Secret to Transferring Gift Cards to PayPal!

Unleash the potential of that neglected gift card by transferring its value to your PayPal account! Not only will you bolster your balance, but you’ll open up a world of spending opportunities. But how does one go about making this shift? Let’s dive into the basics so you can make the move with

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-06-01 00:10:31
Showing page 1 of 10

VyConvert - since 2022
, US

Facebook| | DMCA

Gen in 0.0774 secs