Date to text

[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!

 

[Anders S]

Format>Number>Custom = yyyymmdd ?

HTH
Anders Silven

 

[Guest]

Thanks for the response Anders but changing the format
only changes the "appearance" of the cell contents, not
the actual values.

-----Original Message-----
Format>Number>Custom = yyyymmdd ?

HTH
Anders Silven

"Bmunny" <[email protected]> skrev i

meddelandet news:[email protected]...

 

[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.

 

[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.

 

[Peo Sjoblom]

David's suggestion works fine for me..
You need double minuses

 

[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????

 

[George Nicholson]

I believe that how dates show up in the Formula bar is controlled entirely
by your regional settings.

So I think the answer is no, not without changing your regional settings.

In any case the "actual value" of the cell isn't either 20030207 or
2003-02-07. The actual value is 37659 that is simply being DISPLAYED as
20030207 or 2003-02-07. But it is obviously that displayed value that the
"other application" is using when it imports.

So, to give the other application what it wants:
To restate dates as a number but in the yyyymmdd format, add a helper column
and use this formula:
=YEAR(A1)& RIGHT("0" & MONTH(A1),2) & RIGHT("0" & DAY(A1),2)
to restate your dates. If you do this, Excel won't recognize the restated
values in that column as dates (but depending on what you are doing you may
be able to keep both the original and restated values).

If you need to force the date values to text values in yyyymmdd format, wrap
the above formula in the text function =TEXT(above formula,"00000000")

Hope this helps,