NEGATIVE DATES IN EXCEL

T

TJV1960

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

Fred Smith

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))
 
G

Gord Dibben

Data>Text to Columns>Next>Next>Column Data Format>Date>YMD and Finish.


Gord Dibben MS Excel MVP
 
Top