How to enter time in a cell of excel?

Question

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.

Answer 1

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.

1. Add the fractional seconds, converted to days, to the result of the TIME function. For example:

=TIME(6,28,38)+0.117/(24*3600)

2. Use the TIMEVALUE function which accepts fractional seconds:

=TIMEVALUE("6:28:38.117")

Answer 2

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.