When cells with dates don't format

[Guest]

I have some cells which contain dates. These dates are a link from another
data source (Bloomberg) and when I try to formatt the cells, I cannot. They
will only display the date in mm/dd/yyyy formatt. Any ideas? Thanks

 

[Guest]

My first guess is that the cells are coming in as text. Look for an
apostrophe (" ' ") in front of the data in the cell. If not, try using "text
to columns" (select the entire column, click Data, Text to Columns, Finish).
Then, attempt to re-format the cells (or entire column) as a date with the
form that you want.
Also, make sure that the cells aren't protected (they shouldn't be if
this is your own spreadsheet).

Steve in Ohio

 

[JulieD]

Hi Woody

sounds to me they are really in the workbook as text, not as a date ... if
you right mouse click on one and choose format / general you should see a
number somewhat like 38314 (for Nov 23, 2004). If you don't see a number
like this, select an unused cell somewhere (maybe on another sheet) and
choose copy, select your cells and choose edit / paste special - ADD, click
OK

now your dates should be dates and you can format them via format / cells.

hope this helps
cheers
JulieD

 

[Guest]

=+blp(D70&" Corp","Maturity",,,BLP|M!'0640P1AQ6 Corp,[MATURITY]') This is
the formula in the cell. If you look at the part after ,,,BLP you can see
there is a ' before and at the end so it may come in as text, but I have
other formulas that are the same way and they do not come in as test. I tried
the Text to Columns but it just writes the formula out in the cell. Any
other suggestions? Thanks

 

[Guest]

Julie, i checked and they are coming in as text, however, do to the
constraints of the sheet I need to have them formatted where the cell is.
What is odd, is that I can use these text dates in calculations. For example
I can subtract one date from the other to get # of days between them. I
didn't think you would be able to do that if it was pure test. I don't know.

 

[Guest]

you can get the value for the data with the function datavalue and format
this number as a date in the format you want.

 

[Guest]

Gary, VALUE did it. Thanks to all of you.

you can get the value for the data with the function datavalue and format
this number as a date in the format you want.