Strange Excel Problem need help with..

K

KHRS

I have an excel sheet with dates in a row of cells (example 06/14/2004)
when i click on the any cell with a date; as you know the data is also
displayed up in the formula bar. But the date up in the formula bar has
a ' before it. (example '06/24/2004). That doesn't show up on the
sheet. Does anyone know why this is happening and also how to do a
batch edit to get the ' out of the dates. Ive tried a find and replace,
but the it doesn't see ' in the cells. Any help would be great.

Thanks
 
T

T. Valko

The ' is a TEXT prefix. That means the "dates" are really TEXT strings.

Here's one way to fix it and convert the strings into real Excel dates. It
may seem like a real clunky way to do it when reading but in practice will
only take a few seconds to execute.

Select the range of cells in question.
Goto Edit>Copy
Find a place in your sheet that is not being used. What you want to do is
paste the copied cells transposed so that they will be in a column rather
than a row.
So, select a cell where you can paste then do:
Edit>Paste Special>Transpose

With the new range still selected
Goto Data>Text to Columns
Next>Next>in step 3 of the wizard select DATE>Finish

This will convert the strings into real Excel dates.

Now copy these new dates and then paste special>transpose OVER the original
"date" strings.

Then delete the column of dates.

Sounds like a lot but it only takes seconds to complete. I can do it in
under 15 seconds!

Biff
 
R

Ron Rosenfeld

I have an excel sheet with dates in a row of cells (example 06/14/2004)
when i click on the any cell with a date; as you know the data is also
displayed up in the formula bar. But the date up in the formula bar has
a ' before it. (example '06/24/2004). That doesn't show up on the
sheet. Does anyone know why this is happening and also how to do a
batch edit to get the ' out of the dates. Ive tried a find and replace,
but the it doesn't see ' in the cells. Any help would be great.

Thanks
Click to expand...

They are really text strings that look like dates.

To convert them to real, Excel dates

1. Select a blank cell
2. Edit/Copy
3. Select the cells that have these dates in them.
4. Edit/Paste Special/Add

This may result in a numbers being displayed, e.g. 38809 39416 etc.

If so, you will need to format the cells as dates:

Format/Cells/Number/Date (and select an appropriate format)
--ron
 
Top