How to format a date as text?

[Fiona]

I want to use Datevalue. To use this, the date must be formatted as text. The
only way I can get Datevalue to work is to insert an apostrophe (') before
the date, manually. There must be an easier way? (I have 30,000 lines of data
to format).
Any ideas?

Thanks,

Fiona

 

[Pete_UK]

If you already have dates, can't you just format the cells as number
to get the serial value?

Pete

 

[Peo Sjoblom]

DATEVALUE is a totally obsolete function except for pedagogical reasons, you
can use VALUE (no necessary either but shorter) or just coerce any text
string using multiplication, adding or unary minuses

=--(string)

=DATEVALUE(string)

However it seems strange that you want to take a numerical date, convert it
to text and use a formula to convert it back to a numerical date?


--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

Any ideas?

First idea is that you may not need to use Datevalue (I've almost never
used it in any app I've created), so therefore don't need to reformat at
all. But you didn't post what your ultimate aim was, so it's hard to
tell.

You could, in an empty column use

=TEXT(A1, "mm/dd/yyyy")

(or whatever format you want), then copy that formula down as far as
required. Copy that column and Paste Special/Values over the originals.

 

[Rick Rothstein \(MVP - VB\)]

It would help us if you told us what you are ultimately trying to do. I
doubt that changing your data from dates to text is necessary.

Rick