converting dates into numbers

K

kikilein

Hi,

I have a column that includes some real dates (1/12/2001 for example) and
some that are a little different (9/00/2003 or 00/00/0000). I would like to
sort this column in chronological order and have difficulties doing so. Some
of the "real" dates are formatted as dates and some of the "little different"
dates are formatted as General and the sorting is totally unrealiable. Right
now, if I were to convert all the dates into General the date 9/18/1998 would
convert to 36056, which is NOT what I want. I want to convert it to
9/18/1998.

Is there a way to format the column in such a way that the dates are
converted into numbers but the date format stays (instead of getting
 
R

Richard O. Neville

If you try to apply a date format to a number that can't be a date, it won't
work. Example: if you erroneously key in "9-31-00" Excel will not recognize
it, as September has only 30 days. How can you "chronologically" sort a
number like 9/00/2003 when it isn't part of any chronology? Try applying the
format m/dd/yyyy to 9/18/1998 and you will see that the format won't change.

Whatever date format you apply, Excel still STORES each date as a number
like 36056, by which dates are sorted (again, regardless of date format and
what you see on the screen). I can't think of any way to sort non-dates
along with real dates because the non-dates would not have five-digit code
numbers.

Could it be that your dates like 9/00/2003 were keyed in by someone who
wanted to indicate simply the month of September (say, as an expiration
date), rather than a specific day in September? If you changed this to
9/30/2003 it might solve your problem.
 
M

Myrna Larson

The items that are "a little different" aren't dates. They are text. True
dates are stored as the number of days elapsed since Dec 31, 1899 (which
explains the number 36056 that you mention). Changing the formatting doesn't
"convert" the date. It just shows that number without any special formatting.

If you are using 0s to indicate that part of the date is unknown, you MUST
specify something for that part to make it a "real" date. For example, if you
type Sep 2004 in a worksheet cell, Excel generates the value for Sep 1, 2004.

First of all, I would clear all cells containing 00/00/0000. Used Search and
Replace to do that.

Then, assuming that for the rest the only missing part is the day of the
month, change it to the 1st, like Excel does, by searching for /00/ and
replacing with /01/.

Once you've done that, your dates will sort correctly, with the empty dates at
the bottom.
 
Top