Date to text

B

Bmunny

Hi All

Can someone please help me out with something that's been
puzzling me for quite some time?

I have a column of dates that I've imported into Excel
from another application. On the spreadsheet, the format
is yyyymmdd so that's fine. When I go into a cell, on
the formula bar, it shows up as yyyy-mm-dd. My regional
settings for dates is yyyy-mm-dd. Without changing my
regional settings, can I get the actual value of the cell
to be "yyyymmdd" not "yyyy-mm-dd"?

For example, in cell A1, the contents appear as
20030207. Going into the cell, the formula bar shows
2003-02-07. I want the actual value of the cell to be
20030207. I don't care if it's in number or text
format. If I simply format the cell to be in number or
text format, the value then becomes 37659, which is the
number representation of the date 2003-02-07.

Please help as this is very frustrating. I can only get
the contents to the desired format if I use a macro to
create the string '20030207'. I don't want to use a
macro to do this.

Thanks in advance!
 
D

Dave Peterson

How about a helper column(s).

=text(a1,"yyyymmdd")
for text
or
=--text(a1,"yyyymmdd")
for numbers

Then drag down and convert to values.

Then delete the original column.

But most people would want to keep that date as a real date. You can do more
things with it easier.
 
G

Guest

Thanks a lot Dave. I got the first one to work but I
couldn't get the second formula to work. I had
originally used the text function but it didn't work for
me as I had the entire column formatted to text. I
inserted the formula into another column and it worked
fine. You're right in that keeping them as true dates is
better off but I need to import them into another
application but the application doesn't like the "-" or
any other symbols.

Thanks again.
 
D

Dave Peterson

The double minus signs convert the text to a numeric value. I'm not sure why
the second formula didn't work when the first did????
 
Top