Forum Discussion
Time values with decimal seconds
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....
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)