Turning Date Format into Text

Topic

New Reply

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

Reply | Quote

Replies

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

--Joe

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

ussrankin

Reply | Quote

YESSSSS!

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

Reply | Quote

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.

Reply | Quote