How to format cells to use hh:mm:ss.000 correctly?

I am working with Excel 2013 and I have a column with time in seconds in the format 0.013, 0.033, etc. I would like to show this information in the the format 00:00:00.013, 00:00:00.033. But I am struggling and I don't get any result. When I set up the correct format, the software automatically changes the values. I have attached a picture which may help you to understand the problem.

How could I tell the software that the values are in seconds, not in hours?

3 Answers

As @fixer1234 commented, Excel stores dates and times as a number (possibly fractional) of days.  If you have data that are numbers of seconds, you must divide by 86400 (the number of seconds per day, =24×60×60).  Two ways to display the result you want are

• Set B1 to =A1/86400 and format it as [hh]:mm:ss.000, or
• Set B1 to =TEXT(A1/86400, "[hh]:mm:ss.000").

The 0.013 is 13 seconds? If so why do you want it as 00:00:0.013 and not 00:00:0.013?

Once you have that decimal number I don't think there is a way to 'format' it as time. However you could use a formula to convert it to a time value.

Use: =REPLACE(SUBSTITUTE(A1,".",""),3,0,":")

A cell with text format can be used to include milliseconds in a time entry. Set the entry cell to text format. Enter digits as mm:ss.000 The text entry is treated as a time and can be correctly converted to seconds using for example = (cell ref) /86400 Excel recognizes the text string in the cell as a time. The Problem (excel 2003). The entry cell format can be set to time and mm:ss.000 and a value entered in this format. Any edit of the entry causes Excel to round it to mm:ss only. The millisecond information is lost