Format a date

  • Thread starter Thread starter Kathie
  • Start date Start 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!
 
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))
 
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
 
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.
 
That worked ok for me if the data was text ('02032003), but not if it was
numeric (formatted to show that leading 0).
 
Back
Top