Cell modification

  • Thread starter Thread starter Dave Peterson
  • Start date Start date
D

Dave Peterson

Are those values really dates/times or are they text?

I couldn't get xl2003 to show that many decimal places if the value was really a
number.

I could convert date/time to a time by using:

=mod(a1,1)
and applying a custom format of:
hh:mm:ss.000

But I saw this in the cell:
15:00:10.309

If the value were really text, I could use:
=mid(a1,12,255)

(255 is just a big number so that I get the remainder of the text)
 
You also have your date set wrong so that it puts your post at the top....And, it's html I delete those.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hello,

I have cells in General format with the content 2008/06/23 15:00:10.308668. Is it possible to modify cells to the view 15:00:10.308668 by tools or functions of Microsoft Excel or it is necessary to write macro?

Sincerely,
Aleksander
 
Aleksander said:
I have cells in General format with the content 2008/06/23
15:00:10.308668.
Is it possible to modify cells to the view 15:00:10.308668 by tools or
functions
of Microsoft Excel or it is necessary to write macro?
Click to expand...

Ostensibly, you could use the Custom format h:mm:ss.000. That would display
15:00:10.309.

There are ways that you could display the date/time value to 6 decimal
places. But if you truly entered 2008/06/23 15:00:10.308668 as time, not
text, Excel rounds it to milliseconds. So there is no point to increasing
the displayed precision.

As you may know, if you enter the date and the time in the same cell and
merely change how it is displayed, it will not equal the time value
15:00:10.309. The date/time value above is about 39622.6251193171, whereas
the time value alone is about 0.625119317129629.

But also note that even if you "subtract" the date portion, the resulting
time value might not equal the value of the time portion alone. For
example, If the above date/time is in A1, MOD(A1,1) is about
0.625119317126518, which Excel recognizes as different from
0.625119317129629, the time value of 15:00:10.309.

BTW, that is true for date/time entered with much less precision. For
example, you enter 2008/6/23 15:01 into A1, MOD(A1,1) does not equal the
time value of 15:01. That is, Excel recognizes them as different.
(Sometimes Excel recognizes different values as equal, if they are "close
enough".)

In conclusion, if you want to maintain time to that kind of precision, enter
the date and time in separate cells. Moreover, if you want to maintain time
with more than millisecond precision, you will need to enter it as text. In
that case, yes, you will need to rely on macros to manipulate the
time-as-text value.
 
Hello,

I have cells in General format with the content 2008/06/23 15:00:10.308668. Is it possible to modify cells to the view 15:00:10.308668 by tools or functions of Microsoft Excel or it is necessary to write macro?

Sincerely,
Aleksander
 
Back
Top