Format a date

K

Kathie

I have a column of dates - 8 digit, no slashes -
example:
02032003
02052003
etc.

I want to have them display 02/03/2003. When I go to
format-cell-date-3-14-1998, I either get ########### (and
the column is plenty wide enought for the new date to
fit) or I get some really bizarre numbers - 02012003 is
converted to 9/3/7408.

Any ideas what is going on?
Thanks!
 
E

Earl Kiosterud

Kathie,

If you want to use date formatting, your dates must be Excel date-time
serial numbers. Yours aren't. See www.cpearson.com for details on how that
works. You can convert your "dates" to real XL dates with
=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,3,2))
 
R

Ron Rosenfeld

I have a column of dates - 8 digit, no slashes -
example:
02032003
02052003
etc.

I want to have them display 02/03/2003. When I go to
format-cell-date-3-14-1998, I either get ########### (and
the column is plenty wide enought for the new date to
fit) or I get some really bizarre numbers - 02012003 is
converted to 9/3/7408.

Any ideas what is going on?
Thanks!
Click to expand...

02032003 is a number, not a date. You need to convert it to a date that XL
understands. One way to do that is with the formula:

=DATE(MOD(A1,10^4),INT(A1/10^6),MOD(INT(A1/10^4),100))

Then you can format the result as a date

XL stores dates as the number of days since a base date. 9/3/7408 is 2012003
days since 1/1/1900

Some numbers display as ####### because they translate to a date after
12/31/9999 which is the limit for XL


--ron
 
D

Debra Dalgleish

To convert the column to dates that you can format, you can use Text to
Columns:

1. Select the column with dates
2. Choose Data>Text to Columns
3. Click Next, click Next
4. In step 3, choose MDY from the Date dropdown.
5. Click Finish.
 
D

Dave Peterson

That worked ok for me if the data was text ('02032003), but not if it was
numeric (formatted to show that leading 0).
 
Top