In this photo, as you see I have entered the formula =TIME(6,28,38.117)
in the B33
cell. But I don't understand why the content is shown like: 28:38.0
.
I mean why the hour isn't shown and how can I set excel in order to not approximate the data and print all those 3 decimal digits for the seconds?
I'm using Microsoft Excel 2010 on a win7x64 machine.
2 Answers
There are two things you need to change to display the hour and the 3 decimal digits for the seconds.
First, format the cell as hh:mm:ss.000
for 06:28:38.117
, or h:mm:ss.000
for 6:28:38.117
(i.e., hours with or without a leading zero). Add these special time formats in the Custom category of the Format Cells... window.
The other problem is that the TIME
function accepts whole seconds and quietly ignores the fractional part. There are two ways to work around that limitation.
- Add the fractional seconds, converted to days, to the result of the
TIME
function. For example:
=TIME(6,28,38)+0.117/(24*3600)
- Use the
TIMEVALUE
function which accepts fractional seconds:
=TIMEVALUE("6:28:38.117")
-
-
1Go the Custom category, not the Time category, on the Format Cells window. That's where you can type or paste the formats. I'll update the answer.– creidhneAug 8, 2016 at 16:56
-
yeh, i did it before your comment. with the custom, both of your tricks work. thanks a lot. Aug 8, 2016 at 17:03
Have you tried formatting the cell (B33) to your liking?
Right-click on the cell, select Format Cells..., and select the category and formatting option you desire.