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

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

Pete

On Oct 10, 3:37 pm, Fiona wrote:
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

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.

In article ,
Fiona wrote:

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

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


"Fiona" wrote in message
...
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

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


"Fiona" wrote in message
...
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