I have a spreadsheet with what excel refers to as a negative date, example:
20030112. What I want to do is format the date as 2003/01/12 or 01/12/2003,
but I can't find anything that works. I've used the format date function, the
DATEVALUE function; formatted the date as text, numbers to attempt to
reformat as a date. Is there a faster way to do this instead of manually
placing a "/" to separate year-month-date.

You need to convert your number (20030112) to an Excel date. Then you can format
it. Try:

=date(left(a1,4),mid(a1,5,2),right(a1,2))

--
Regards,
Fred


"TJV1960" wrote in message
...
I have a spreadsheet with what excel refers to as a negative date, example:
20030112. What I want to do is format the date as 2003/01/12 or 01/12/2003,
but I can't find anything that works. I've used the format date function, the
DATEVALUE function; formatted the date as text, numbers to attempt to
reformat as a date. Is there a faster way to do this instead of manually
placing a "/" to separate year-month-date.

DataText to ColumnsNextNextColumn Data FormatDateYMD and Finish.


Gord Dibben MS Excel MVP

On Thu, 22 Feb 2007 18:01:00 -0800, TJV1960
wrote:

I have a spreadsheet with what excel refers to as a negative date, example:
20030112. What I want to do is format the date as 2003/01/12 or 01/12/2003,
but I can't find anything that works. I've used the format date function, the
DATEVALUE function; formatted the date as text, numbers to attempt to
reformat as a date. Is there a faster way to do this instead of manually
placing a "/" to separate year-month-date.