• Turning Date Format into Text


    I’m feeling stupid on this.

    I have a column of dates in this format: 3/3/2010 9:41:50 AM

    I want to set up another column of the same information, but formatted as 3/3/10, preferably as text, or something else that drops the time. Also acceptable would be 03/03/10.

    How can I do this? None of my tries have worked.

    Lou Sander

    Viewing 6 reply threads
    • #2419683

      Rather than waste time telling you things you’ve already tried, tell us what you have tried that does not work.


    • #2419691

      Oh my! It’s been a while, and I’m just now revisiting it. I’m fooling with it now as I type.

      Mostly I’ve tried pasting into cells with text format. It shows up as text, but when I click the cell, the status bar includes the time.

      I think the problem starts with wherever I got the original data. It had the time in it, and I don’t seem to be able to get rid of it.


      Lou Sander

    • #2419757

      Highlight the column.

      Right click and select Format Cells

      On Number page under Category select Custom as shown below

      Highlight whatever is in the box below Type:

      Enter mm/dd/yy and press OK

      This will give date in the format you wanted.


      HTH, Dana:))

      HTH, Dana:))

    • #2419774

      I am starting to figure this out, but I still don’t have what I need.

      I’m now able to get my dates to appear as August, 2018, etc., which I want to use in an email to be sent by Constant Contact.

      That item has to be pasted into Constant Contact as text. The problem is that in Excel, it is still basically in Date format, no matter how I format it. I can display it any way I want, but is at root a Date. When I try to paste the Excel date into Constant Contact, I get an error.

      What I need is to convert the formatted date into text that can be copied, pasted, etc. without being some sort of Excel date.

      Lou Sander

    • #2419782

      OK, I understand what you want.

      Create a separate column next to the Date column.

      Use the TEXT function in Excel to convert the date to a date in text format.

      If A1 has the Date in date format and B is the next column with empty cells.

      In B1 enter the following formula =TEXT(A1,”mm/dd/yy”)

      This will take the date entered in A1 as 01/01/22 and place it in B1 as 01/01/22 but the date is now in text format.  This B1 text date should copy and paste as text.

      You can use the copy paste formula function to apply this formula to the other rows.

      HTH, Dana:))

      HTH, Dana:))

      1 user thanked author for this post.
    • #2419837


      That does it!

      I’ve been trying to do this on and off since 2009. I was looking for a way for my emails to say “You have been a member since 10/11/12. (Or whatever.)

      One other thing that could/should be done is to format Column C as Text. Then Copy Column B and Paste Special Values into Column C. Then you are done forever with the tricky date formatting, and you can delete all those other columns with dates in them.

      Lou Sander

    • #2419950

      Newer versions of Excel have “Power Query” available, which has a lot of methods for converting dates. Some web searching will lead to article explaining how to do the various conversions.

    Viewing 6 reply threads
    Reply To: Turning Date Format into Text

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: