I can't convert from mm/dd/yy to month dd, yyyy

[dodgers32]

I have a list of dates just over 1000 lines long. Dates range from
01/01/1993 to 12/31/2003. When I attempt to convert the column from
01/01/1993 to January 1, 1993 only some of them (about 30) will convert. The
rest don't change at all. I've tried going to other formats and the same
data changes each time. Help...

 

[Conan Kelly]

dodgers32,

Are the dates that do not change actually entered as text?

If you widen the column so there is more than enough room, format the whole
column with a "General" number format, and make sure there is no specific
alignment (left, center, right, justified) set on the whole column, then
numbers and dates will be right aligned in the cells & text will be left
aligned.

I'm guessing that the dates that do change will be right aligned and the
ones that don't will be left aligned.

Select the ones that don't change (one at a time). Look in the fomula bar
for each one. If they start with an apostrophe ('), then they are stored as
text.

If there are 's in them, then selecting the whole column and doing a
find/replace ([Ctrl] + H) might solve your problems: Find what: = ' (a
single apostrophe) Replace with: (leave blank). then click Replace All.

HTH,

Conan

 

[David Biddulph]

Some of the "dates" are probably text, rather than dates. You might try
Data/ Text to Columns, specifying Date MDY as the incoming format at the
last stage of the Wizard.
This may, of course, be a symptom that you've tried to import data that was
in the opposite format from that specified by your Windows Regional Options.
If your data was in mm/dd/yyyy and your Windows settings were looking for
dd/mm/yyyy, some of the numbers will have been misinterpreted (Feb 10th
instead of Oct 1st), but the others (like 12/31/2003) would stay as text.
If that's what's happened, reformatting won't help where you've already
stored the wrong number, so change your Windows settings and reimport the
original data.