converting time to text and istext() question

[lezawang]

Hi
I entered 1:00 and then 2:00 in cells B2 and B3, then in B4, I used = sum(B2,B3).

[TABLE="class: grid, width: 295"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]istext()[/TD]
[/TR]
[TR]
[TD]time1[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]time2[/TD]
[TD="align: right"]2:00[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]total[/TD]
[TD="align: right"]3:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so far so good, then I highlight cells B2 and B3 and changed the format to "Text"

[TABLE="class: grid, width: 295"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]istext()[/TD]
[/TR]
[TR]
[TD]time1[/TD]
[TD]0.041666667[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]time2[/TD]
[TD]0.083333333[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]total[/TD]
[TD="align: right"]3:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Both times changed to number and moved to the left hand side to indicate they are Text now! however
istext() is still telling me, they are not text. What I need to do is to double click on cell B2 and B3 and only after that istext() will be true. See the table below after double clicking both B2 and B3. My question, why I needed to do the double clicking. When istext() is still false in the above table but in the below table is true (after double clicking B2 and B3). Thank you.

Now if I double click on B4, I will get this

Again why only after I doubled click on B4, now b4 is showing formula as a text? I do not understand the double click thing. Why I have to do that to make the change. Why switching format wont do it right away.

For example if A1=1 and I change the format to date then I will get 1/1/1900 and if I change the format back to general I will get 1. Without needing to double click etc. Only when it is time I need to do that? Why is that. Thank a lot indeed.

 

[sandy666]

change blank cells B2 & B3 to text then enter time
B4 change format to hh:mm then enter =SUM(--B2,--B3)

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td][/td][td]istext[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]1:00[/td][td]

TRUE​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]2:00[/td][td]

TRUE​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]

03:00​

[/td][td]

FALSE​

[/td][/tr]
[/table]

or explain what you want to achieve

 

[lezawang]

Thanks for your reply. What I want to achieve is to learn how to convert time saved as a text to number value. What I have not been able to understand, the Double Click thing. If I entered a time in a cell and then changed the format of the cell to a text, Excel wont show it as a text until in double click and then exit from the cell, then I will see the green triangle.

The same thing, if I format a cell to a text and then enter a time then when I change the cell format to general, excel wont show the change until I double click the cell then exit from the cell then it will be convert it to a number.

By the way, when I did exactly what you did the sum() is showing 0 not 3? but that is not my concern because I know why it is showing 0 but the double click thing I do not really understand. Thank you very much.

 

[sandy666]

What I have not been able to understand, the Double Click thing

Ask Excel Support, they should be able to explain that in details

 

[sandy666]

maybe in short:

re-enter == F2(edit) then enter == dbl click(edit) then enter

 

[joeu2004]

If I entered a time in a cell and then changed the format of the cell to a text, Excel wont show it as a text until in double click and then exit from the cell, then I will see the green triangle.

First, I suggest that you ignore the green triangle. I'll explain why below.

Second, simply changing the format of a cell does not change the __type__ of the value of the cell. If it was numeric, it stays numeric. If it was text, it stays text. Changing the format only changes the appearance of a vaule.

However, if we "re-enter" the content of the cell (constant or formula), the current format will apply. If the cell format is Text, numeric values become text. If the cell has a numeric format (General, Number, etc), text values become numeric if Excel can interpret them as such. And that might depend on regional and language settings.

Apparently, your Excel recognizes double-click as a way to "re-enter" the cell contents. That does not work with my Excel configuration. It probably depends on how one or more Excel Options are set. For my Excel configuration, I must click on the cell (select it), press function key f2, then press Enter (or ctrl+shift+Enter for array-entered formulas).

-----

With text data that Excel can interpret as numbers, =SUM(--B17,--B18) should have produced the correct sum. To see it, be sure to format the cell properly. Cells with time calculations should always be formatted as [h]:mm or [m]:ss or something similar. The "[h]" displays hours > 23. The "[m]" displays minutes > 59. The most precise time format is [h]:mm:ss.000 , which displays seconds to the millisecond.

In general, any text that Excel can interpret as a number can be used directly in arithmetic expressions without conversion. However, sometimes VALUE() can interpret unusual numeric text that Excel arithmetic cannot, because VALUE() is more tolerant of errant spaces.

However, numeric text is __not__ interpreted as a number in comparisons. Suppose A1 has ="12:34", which is 12h 34m in text form. A1=TIME(12,34,0) returns FALSE because A1 is text and TIME is numeric. But A1+0=TIME(12,34,0) returns TRUE because A1+0 is numeric.

-----

About those green triangles....

Excel mistakenly calls them "error checks". They are __not__ errors. They are merely warnings. Excel is trying to be helpful: telling you when __Excel__ thinks something is inconsistent.

But 99 times out of 100, there is nothing wrong. For example, if you have numbers in A1:A10 and you enter =SUM(A1:A5) in B1, Excel puts a green triangle in B1 with the warning "formula omits adjacent cells". Well, yes. But we did it on purpose.

I find the "error checking" annoying. So I disable it.

In Excel 2010, click File > Options > Formulas and uncheckmark "Enable background error checking". Also click "Reset ignored errors" to clear all existing green triangle.

Sleep better at night! (smile)

 

[lezawang]

Thank you all for your help. That is really great. Thanks once again

First, I suggest that you ignore the green triangle. I'll explain why below.