Converting Dates to Text

by Allen Wyatt
(last updated August 7, 2020)

2

Debbie has a worksheet that has many dates in it. To concatenate data with the dates, she has to edit each cell that contains a date and add an apostrophe before the date. She wonders if there is a quick way to do this. She has tried highlighting the row and formatting it as text, but that did not work.

If Debbie wants to automate the manual work she is doing, it could be done with a macro. The following automatically converts the selected cells to text and stuffs the date back into the cell.

Sub DateToText()
    Dim cell As Range
    Dim sTemp As String

    For Each cell In Selection
        With cell
            sTemp = .Text
            .NumberFormat = "@"
            .Value2 = sTemp
        End With
    Next cell
End Sub

Note the use of the .Value2 property instead of the .Value property when stuffing the text back into the cell. This is done to ensure that Excel doesn't inadvertently convert the text back into a date value. It shouldn't do it anyway, but the .Value2 property doesn't support the Date data type, so any potential conversion is, by nature, precluded.

You should also note that the macro will convert anything that you have selected, even if it doesn't contain a date. You'll want to make sure that you select only cells containing dates before running it.

Of course, the easiest way to do what Debbie is trying to do is to use the TEXT worksheet function. Suppose, for instance, that cell A1 contains a date. In a different cell you wanted to put the words "The deadline is " before the date. You could do that with a formula like this:

="The deadline is " & TEXT(A1, "mmmm d, yyyy")

Note that the TEXT function requires two parameters: The cell to be converted (A1, in this case) and a pattern to use in the conversion ("mmmm d, yyyy"). If cell A1 contains a date such 6/25/16, then the formula returns the following:

The deadline is June 25, 2016

The pattern you specify, within quotes, follows very closely the patterns you would use in defining a custom format for a cell. For instance, "mmm" returns a three-character month name while "mmmm" returns a fully spelled-out month name. The codes you would use in the pattern have been covered in other ExcelTips, but you can also find a handy summary of codes that conveniently correspond to what Excel uses at Wikipedia:

https://en.wikipedia.org/wiki/Date_format_by_country

An interesting point regarding this approach is that it doesn't really matter how the date in cell A1 is formatted. When you use TEXT, it relies on the underlying serial number used by Excel to store the date, and then formats it according to the pattern you specify—independent of any formatting in the cell containing the date.

Of course, if you want to do a straight conversion to text, you don't have to concatenate anything with the date. In this case, you would just use the TEXT function by itself:

=TEXT(A1, "mmmm d, yyyy")

The TEXT function, by nature, returns a strictly text value.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13450) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Tools on Developer Tab are Unavailable

Want to add some macros to your workbook? What do you do if you try to add the macros but the program has disabled the tools?

Discover More

Make AutoCorrect Pay Attention to Character Case

If you rely on AutoCorrect (as most Word users do), you may have noticed that it doesn't always give the desired results ...

Discover More

Working with E-mailed Documents

Ding! You've got mail. That mail has a Word document attached to it. Before you rush off and open that document, take a ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Preventing Automatic Date Formatting Changes

Excel often changes the formatting of a cell based on how it parses what you are entering into that cell. This is ...

Discover More

Number Formatting Shortcuts

Keyboard shortcuts can save time and make developing a workbook much easier. Here's how to apply the most common of ...

Discover More

Adjusting Center Across Selection with a Cell Value

One of the most common cell alignment settings I use is the one to center across a selection. If you want to vary how ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 + 2?

2016-06-27 08:37:15

LJR

I often use the text-to-columns function if all the data in 1 or more columns needs to be converted.


2016-06-27 02:08:47

Ru

You could check whether the cell is a date and only then do the conversion.

Sub DateToText()

Dim cell As Range
Dim sTemp As String

For Each cell In Selection
If IsDate(cell) Then
With cell
sTemp = .Text
.NumberFormat = "@"
.Value2 = sTemp
End With
End If
Next cell

End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.