EXCEL

[KEVIN]

DOES ANYONE KNOW HOW TO GET A DATE FORMATTED TO MM/DD/YYYY
WHEN IT COMES TO YOU AS AN UNFORMATTED FIELD AS FOLLOWS

20031202 (YYYYMMDD)

THANKS KEVIN

 

[Jason Morin]

Try:

=TEXT(A1,"00\-00\-00")*1

and press Ctrl+1, and format as mm/dd/yyyy.

HTH
Jason
Atlanta, GA

 

[Guest]

KEEPS TELLING ME THAT NEGATIVE DATES OR TIMES ARE
DISPLAYED AS ##########

 

[Harlan Grove]

KEEPS TELLING ME THAT NEGATIVE DATES OR TIMES ARE
DISPLAYED AS ##########

Stop posting in all caps. It's considered shouting, and if you do it often
enough, you may be ignored or receive nothing more than helpful advice on how to
use the newsgroup responsibly but little or nothing with regard to your Excel
problem.

Given the sample date from your original posting, 20031202, and if it were in
cell A1, Jason's formula

=TEXT(A1,"00\-00\-00")*1

(which could be shortened to =TEXT(A1,"0\-00\-00")*1, but I digress) converts
the result into a date serial number. If you're trying to use double unary
minuses rather than multiplication by 1, then be sure you have *TWO* (note the
appropriate use of SHOUTING for emphasis) minus signs.

=--TEXT(A1,"00\-00\-00")