double click a cell with time as a value

[lezawang]

Hi

I have a cell D4 which has value = 40:03:28
The value is aligned to the right side of the cell, which gives me an impression it is a number, right? When I one click (select) that cell. I see the following:

cell value = 40:03:28
Formula bar = 40:03:28

Home tab-->Number group --> I see General changed to Custom and when I go to Format cell, I see [h]:mm:ss selected

When I do E4 = timevaule(D4) I get 0.66074

If I double click on cell D4 (go to edit mode), then when I click outside the cell and then click back, then I will see the value of cell changed to

01/01/1900 16:03

But the formula bar is showing this value

01/01/1900 4:03:28 PM

My question, why when I double click on the cell, the value will be changed from 40:03:28 to 01/01/1900 16:03 ? ​What this phenomenal called?

Why Date is added? and why 16:03 .

I know the 16:03 is coming from 0.66074 *24 but why the double click did that calculation for me? That what I do not understand.

If you like to see the file, I pasted the link. Thank you so much in advance for your help.

https://drive.google.com/file/d/1ZgBLOIf4SvAl1M6tB8eTyNEucsJkd1QJ/view?usp=sharing

 

[jorismoerings]

Hi,

This effect is caused by standard features of Excel and (unfortunately or not) there's not a whole lot you can do about it. However it's not a calculation and it didn't ADD anything. Excel changed a number format.

From the top:

1. The key thing to understand is that number formats change the way numeric values are displayed, but they do not change the actual values.
2. As you enter data, Excel will sometimes change number formats automatically. For example if you enter a valid date, Excel will change to "Date" format. If you enter a percentage like 5%, Excel will change to Percentage, and so on.
3. You can't really edit a custom number format. When you change an existing custom number format (as excel expects when you go into EDIT-mode), a new format is created and will appear in the list in the Custom category.

So with the ground rule in kind and because you're not changing anything, excel responds with rule #2 , your field has a customized DATE format and hold a valid date so Excel will show this as a valid date format in the formula bar.

However, keep in mind rule 1!

Hope this helps.

 

[lezawang]

Thank you very much for your reply. As other MVPs mentioned that the cell (D4 = 40:03:28) has Text value not Number. So when I do =timevalue(D4), I will get 1.66. So clearly that is not number, it is a text. But what I do not understand why excel aligned that text to the Right, which gave me an impression it is a number.

Also when I click on the cell (select it). I see in the formula bar 1/01/1900 4:03:28 PM , why is that? Thank you very much.