Home

Time values with decimal seconds

Norman Martens
New Contributor

I need to be able to enter times with decimal seconds.  For instance, 1:15:25.50 representing 25 and 1/2 seconds after 1:15. I enter the following: =TIME(1,15,25.5) and format the cell as:  hh:mm:ss.ss and it displays:  

01:15:25.25

Why doesn't it display 01:15:25.5?

 

When will Excel support real time stamps.  Almost every language has them.  For instance, this SQL command:

SELECT SYSDATETIME() ; 
 returns

2007-04-30 13:10:02.0474381

 

How do I download time stamp fields from a database to Excel?

 

Thanks.

1 Reply

Hi Norman,

 

Two reasons are here. First, in you custom format hh:mm:ss.ss you repeat showing seconds at the end, thus it show .25 where 25 is number of seconds. To show milliseconds use custom format like

hh:mm:ss.000

when the time will be shown as 01:15::25.500

 

Another point TIME function doesn't work with decimals. If you use decimal numbers as parameters they will be cut to integers. For example, =TIME(1.7,0,0) returns 01:00:00 (it takes integer part of 1.7). Same with seconds if you enter it as 25.5, TIME returns 25 seconds only.

 

To work with TIME and milliseconds you shall add them manually like

=TIME(1,15,25)+0.5/24/60/60

Or you may enter it without using the TIME as 1:15:25.5 (however, in formula bar it'll be shown as 01:15:26)

image.png

image.png

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies