double click a cell with time as a value

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,520
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

Well-known Member
Joined
Jul 4, 2014
Messages
1,263
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.
 
Last edited:

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,520
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.
 

Forum statistics

Threads
1,082,360
Messages
5,364,927
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top