Excel Date Conversions

  • Thread starter Thread starter joshguy875
  • Start date Start date
J

joshguy875

Hi-

I am trying to sort a mailing list that is in Excel by date. The format
of the date as exported by our mailing list program is 01-Apr-02. Excel
knows that this is a date but cannot tell if it should convert to 19 or
20 based on if the number is from the 90's or 2000 and after. Is there
any way to have Excel automatically insert the needed digits
appropriately? Also, is it possible to then convert the formate to
yyyy/mm/dd so that I can sort the list and eliminate all entries over 4
years old?

My ulimate goal is only to remove entries over four years old so if
anyone knows how to do this without having to reformat the date I would
also appreciate knowing.

Thanks much,
Josh
 
Josh,

If these are true Excel dates, it knows whether they're 19 or 20, even
though the formatting may not show the year. Change the date format
(Format - Cells - Number - Date). If they doesn't respond, then they're not
dates, they're just text. Post back, and we'll go from there.
 
Hi-

I originally thought Excel recognized them as dates because if I click
on one, it brings up the little 'i' in the yellow diamond next to the
cell. If I click on the box it gives me a choice to add 19 or 20 the
the beginning of the year. The program the data was exported from is
fairly old and not y2k compliant. Therefore, dates have been entered as
00, 01, 02, and 03. I did try changing the date format, however, but
they did stay the same. This would indicate that it is text.

I don't know if Excel recognizes the cells as dates is due to all of
this (what seems to be) conflicting behavior .

I appreicate the help,

Josh
 
If it is left aligned it is text if it is right aligned it is a numeric
value
Try this, copy an empty cell, select the dates, do edit>paste special and
select add,
if it worked the date you had as an example should now be 37347,
now you can format is as a date using your own prefered date format.
Another thing to try that probably will keep the date format is to use
data>text to colums,
click next twice and in step 3 select date under column data format and then
DMY,
then click finish.
If that does not work you probably have some invisible characters as well,
post back if that's the
case..
 
Thanks!!!

I didn't know about Data>Text to Columns command. I was able to use
this to accomplish what I needed done.

Josh
 
Back
Top