converting time to text and istext() question

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
Hi
I entered 1:00 and then 2:00 in cells B2 and B3, then in B4, I used = sum(B2,B3).

istext()
time11:00FALSE
time22:00FALSE
total3:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

istext()
time10.041666667FALSE
time20.083333333FALSE
total3:00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.

Capture.jpg


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

Capture.jpg


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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
change blank cells B2 & B3 to text then enter time
B4 change format to hh:mm then enter =SUM(--B2,--B3)

B
C
1
istext
2
1:00
TRUE​
3
2:00
TRUE​
4
03:00​
FALSE​

or explain what you want to achieve
 
Last edited:
Upvote 0
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.
 
Upvote 0
maybe in short:

re-enter == F2(edit) then enter == dbl click(edit) then enter
 
Last edited:
Upvote 0
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)
 
Upvote 0
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.
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top