formatting dates
Posted by Doug VanDam on July 03, 2001 10:57 AM
I have a column in date format 1/1/1969 2/15/1999 etc.
I select the entire column by clicking on the top and go to format/cell/number/date and pick a date format Jan-69 and nothing happens. That's the way the help tells you to do it and absolutely nothing happens.
What am I doing wrong?
Thanks.
Doug VanDam
Posted by Scott on July 03, 2001 11:36 AM
The same thing happens to me when I copy data from another source. I have found that the data is actually formatted as text, and has an apostrophe before the date. If this is what's happening to you, you can can remove it using EDIT-REPLACE. In "Find What:" you can put the apostrophe ('), and in "Replace With" you can leave blank. Then select "Replace all".
Posted by Doug VanDam on July 03, 2001 2:06 PM
The data does come from Access but it does not have an apostrophe in front of it.
Posted by scott on July 03, 2001 3:11 PM
The only other things that I can think of is if there is a space before your date begins ie: " 5/2/99". Replace will work with this also.
If the date was formatted as text, for some reason Excel still sees it at text even if you format it (I have this problem sometimes with data from access). You can tell the problem by hitting F2 and then enter after you format it. If it changes to your desired format after doing this, then that is your problem. I'm not sure if this is the best way to fix it, but if you type a zero in a blank cell, copy it, and then Paste special over your data, and select values and add, it will change it from text. I'm sure this is probably not the best way, but it does work.
Posted by IML on July 03, 2001 3:36 PM
You could also try highlight the column and going to data - text to columns.
Hit next, next again, select date MDY and finally finish. This would hopefully "clean it up".
good luck.
