I periodically copy and paste lines of data from a financial web page into Excel. The first field of each line is a date. Some times Excel recognizes these as dates and sometimes it doesn't. As I've learned over time how to control and format dates, I've been able to bring most of them into consistency with the others but not all. I've finally taken the time to look into the odd balls and I'm stumped by what I've found.
When I format the date column, the compliant fields, the ones that Excel stored as dates, fall into line. The odd balls, however, won't respond to any format changes. They just stay the same, e.g., 03/15/2007. I've tried General, Number, Date, Text, and Custom but they won't budge.
I've tried extracting their data with DATEVALUE and VALUE. When I run CELL with "format", it gives whatever format I've put on the cell. When I run CELL with "type", is says "l" which means it's a label.
While I've used names a lot, I wasn't familiar with labels so I did some reading. Labels, like names, provide an alternate way to reference a cell. Labels can be defined with Insert->Name->Label which goes to Label Ranges dialogue. Existing label ranges can be removed using that dialog. I tried defining row and column labels, using them as references, and removing them. Works great. But, a label has to show up in the Existing label ranges list on the Label Ranges dialogue in order to be removable. My odd balls don't show up.
If I just had a few and I weren't curious nor wanting to learn, I could probably delete the cell contents and type the dates in manually. However, I have a slew of them, I am curious, and I want to learn so I'd like to know what's going on here and how to handle it straightforwardly.
For the odd balls I have now does anybody know how to turn these labels into dates? For the future, is there some technique I should add to my copy and paste that would insure I get dates instead of labels?
Thank you.
When I format the date column, the compliant fields, the ones that Excel stored as dates, fall into line. The odd balls, however, won't respond to any format changes. They just stay the same, e.g., 03/15/2007. I've tried General, Number, Date, Text, and Custom but they won't budge.
I've tried extracting their data with DATEVALUE and VALUE. When I run CELL with "format", it gives whatever format I've put on the cell. When I run CELL with "type", is says "l" which means it's a label.
While I've used names a lot, I wasn't familiar with labels so I did some reading. Labels, like names, provide an alternate way to reference a cell. Labels can be defined with Insert->Name->Label which goes to Label Ranges dialogue. Existing label ranges can be removed using that dialog. I tried defining row and column labels, using them as references, and removing them. Works great. But, a label has to show up in the Existing label ranges list on the Label Ranges dialogue in order to be removable. My odd balls don't show up.
If I just had a few and I weren't curious nor wanting to learn, I could probably delete the cell contents and type the dates in manually. However, I have a slew of them, I am curious, and I want to learn so I'd like to know what's going on here and how to handle it straightforwardly.
For the odd balls I have now does anybody know how to turn these labels into dates? For the future, is there some technique I should add to my copy and paste that would insure I get dates instead of labels?
Thank you.