I have saved a file from quickbooks into excel. when I open the report, the
dates in my transaction date column are in text format. When I change the
column to date format, nothing happens until I double click in each cell. It
is as if I have to enter the cell and leave it to 'activate' my command to
change date formats.

this will not do of course, because I have thousands of rows. how do I get
them to all update when I change the format?

When a cell is formatted as one type and you change that format type by
going to the menu FormatCells, the new format isn't applied until you edit
the cell. That's what you're doing when you double click the cell.

Try this...

Select the range of cells that hold the text dates.
Goto the menu DataText to Columns
Click Finish

This will usually reset the format to General and then Excel will recognize
the dates as true Excel dates then automatcally set the format to date.


--
Biff
Microsoft Excel MVP


"BLTibbs" wrote in message
...
I have saved a file from quickbooks into excel. when I open the report,
the
dates in my transaction date column are in text format. When I change the
column to date format, nothing happens until I double click in each cell.
It
is as if I have to enter the cell and leave it to 'activate' my command to
change date formats.

this will not do of course, because I have thousands of rows. how do I
get
them to all update when I change the format?

I'd go just a step further using Biff's instructions.

I'd do the same data|text to columns
then fixed width (but remove any lines)
And choose Date and the correct order (dmy, mdy, ...) just in case the order of
the text entry doesn't match the order of my windows short date.

It may not be necessary in all cases, but those extra 10 seconds would give me
peace of mind!

"T. Valko" wrote:

When a cell is formatted as one type and you change that format type by
going to the menu FormatCells, the new format isn't applied until you edit
the cell. That's what you're doing when you double click the cell.

Try this...

Select the range of cells that hold the text dates.
Goto the menu DataText to Columns
Click Finish

This will usually reset the format to General and then Excel will recognize
the dates as true Excel dates then automatcally set the format to date.

--
Biff
Microsoft Excel MVP

"BLTibbs" wrote in message
...
I have saved a file from quickbooks into excel. when I open the report,
the
dates in my transaction date column are in text format. When I change the
column to date format, nothing happens until I double click in each cell.
It
is as if I have to enter the cell and leave it to 'activate' my command to
change date formats.

this will not do of course, because I have thousands of rows. how do I
get
them to all update when I change the format?

--

Dave Peterson

Hi,

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being entered
as text. Numbers can be stored as text by 1. preformatting the cell to Text
and entering the number, 2. Typing an apostrophy in front of the number '123,
3. Because the data was downloaded from a soure inwhich it was stored as a
number, 4. Because you used the Text to Columns command and converted it to
text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles may
appear at the top left corner of these cell, but this feature may be off or
the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers - but this won't work with dates.
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

If this information is helpful, please click the Yes button.
Cheers,
Shane Devenshire

"BLTibbs" wrote:

I have saved a file from quickbooks into excel. when I open the report, the
dates in my transaction date column are in text format. When I change the
column to date format, nothing happens until I double click in each cell. It
is as if I have to enter the cell and leave it to 'activate' my command to
change date formats.

this will not do of course, because I have thousands of rows. how do I get
them to all update when I change the format?

It sounds like the cells in your transaction date column are not recognizing the date format until you double click on them. This can happen when Excel is not able to recognize the data in the cells as dates. Here are some steps you can take to fix this issue:

1. Select the entire column that contains the transaction dates.
2. Right-click on the selected column and choose "Format Cells" from the menu.
3. In the Format Cells dialog box, select "Date" from the Category list.
4. Choose the desired date format from the Type list.
5. Click "OK" to apply the date format to the selected column.

If the above steps do not work, you can try converting the text dates to Excel dates using the following steps:

1. Insert a new column next to the transaction date column.
2. In the first cell of the new column, enter the formula "
   Code:

   =DATEVALUE(A1)

   " (assuming the transaction date column is column A).
3. Copy the formula down to all the cells in the new column.
4. Select the entire new column and copy it.
5. Right-click on the transaction date column and choose "Paste Special" from the menu.
6. In the Paste Special dialog box, choose "Values" and click "OK".
7. Delete the new column that you inserted.

This should convert the text dates to Excel dates and allow you to format the cells as dates without having to double click on each cell.