I can't add minutes & seconds on imported data

[Rick]

I have used Abby Finereader OCR software to scan a phone bill into
Excel 2000, for the purpose of adding the time spent talking to
various phone numbers.

The OCR software works fine, and I get a column of numbers which
represent the minutes and seconds similar to
01:15
02:10
00:15

The trouble is that I cannot add them as they are. They always add to
zero. I converted the cells format to Custom [h]:mm:ss but they still
will not add.

I then created a helper column, and used the Concatenate function to
add "00:" in front of each of each of the existing values. The column
looked like
00:01:15
00:02:10
00:00:15

I then did a copy and paste special to replace the formulas with the
actual values show above. Although the cells were formatted as
[h]:mm:ss , the values would still not add.

If I select each cell (from the above example), and then click in the
formula bar, and then hit carriage return, the cell display will
immediately change to look like the following, even though the formula
bar continues to display the original double-zero
0:01:15
0:02:10
0:00:15

The column of numbers will now add properly.

So how come a column of numbers formatted as [h]:mm:ss which are
displayed as 00:01:15 will not add, but simply hitting carriage return
in the formula bar will suddenly change it's appearance to 0:01:15 and
allow proper addition. I can't afford to select each cell in the
column, and hit carriage return, so how can I have this automatically
done.

Many thanks in advance.
To reply by email, please remove the first "R" in my return address

 

[Earl Kiosterud]

Rick,

The OCR software probably put your time stuff into the Excel cells as text.
Excel would have gladly changed your times to real times (not text), and
does so to the consternation of folks sometimes when not wanted (and not
always understood). But it didn't get a chance this time. Changing the
formatting has no effect on text -- it has to be real date/time values. See
www.cpearson.com for a good writeup on how dates and times are handled in
Excel. Probably when you did that do-nothing edit (hitting Enter in the
formula bar), Excel jumped at the chance to change it for you.

For a wholesale way of changing them from text to real time values, copy an
empty cell, then select all your time cells, Edit - Paste Special - Add.
That'll likely work in this situation.

 

[Rick]

Earl;

Thanks for the answer ( and in only 23 minutes).

Edit-Paste Special-Add did not work when I first selected an empty
cell, but then I realized the empty cell was formatted as general.
When I used a cell formatted as [h]:mm:ss , everything worked fine.

Many thanks

Rick

Rick,

The OCR software probably put your time stuff into the Excel cells as text.
Excel would have gladly changed your times to real times (not text), and
does so to the consternation of folks sometimes when not wanted (and not
always understood). But it didn't get a chance this time. Changing the
formatting has no effect on text -- it has to be real date/time values. See
www.cpearson.com for a good writeup on how dates and times are handled in
Excel. Probably when you did that do-nothing edit (hitting Enter in the
formula bar), Excel jumped at the chance to change it for you.

For a wholesale way of changing them from text to real time values, copy an
empty cell, then select all your time cells, Edit - Paste Special - Add.
That'll likely work in this situation.

To reply by email, please remove the first "R" in my return address