1

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.

enter image description here

Improve this question
2

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.

format cells

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)
  1. Use the TIMEVALUE function which accepts fractional seconds:
=TIMEVALUE("6:28:38.117")
Improve this answer
3
  • ok, formatting is a good idea. but i could find any format for the leading zero. as this images show: image1 and image2. Also non of the tricks work for showing the decimal (fractional) part of the seconds. image 1 and image 2. – Sepideh Abadpour Aug 8 '16 at 16:38
  • 1
    Go 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. – creidhne Aug 8 '16 at 16:56
  • yeh, i did it before your comment. with the custom, both of your tricks work. thanks a lot. – Sepideh Abadpour Aug 8 '16 at 17:03
3

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.

Format Cells

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.