Excel - round time EXACTLY to nearest second

[TheRobsterUK]

I have many rows of data with time stamps in the form of HH:MM:SS, i.e. 22:35:30. I need to lookup data in this table but the problem I am having is that the lookup values often vary slightly even though look the same, e.g. in actual number terms both 0.663460648148148 and 0.66346064814999 both display as 15:55:23 but the vlookup functions don't work because the ends of the numbers are different.

I plan to use the ROUND function to make the numbers the same. My question therefore is how many numbers should I round to in order to in order to keep the format always correctly in the HH:MM:SS format? This would have to work for any time of the day which is measured down to the second.

Cheers
-Rob

Ps - or any other method that might work but I need the times to be rounded to the nearest second. Thx.

[outofthehat]

As you can see, time is just a decimal, so 1 hour is 1/24, 1 minute is 1/(24*60), 1 second is 1/(24*60*60). According to my Windows 7 calculator (!) that makes a second equal to about 0.0000115, but rounding won't necessarily always give you the right answer. Another thought on the way in a sec.

[K m]

=int(a1*86400)/86400

First we convert the day or fraction of a day into seconds. (86400*A1)

After we have the day into seconds, the INT truncates all the fractions of a second

Next we divide by the number of seconds in a day (86400) to convert the seconds back to days

[dilipandey]

Hi TheRobsterUK,

You may never be sure of that what number of characters from right can be rounded off.... I would suggest you to have one more column in the data table which converts these times as "text" and then use these text enteries for lookup.. If you can upload a sample workbook, I can test this idea. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[outofthehat]

You can convert the time in e.g. cell A1 to an exact time with
<see correction below>
which keep it in numeric format for pivot tables etc, but corrects the times exactly.

CORRECTION
=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

[Jakobshavn]

Consider using the TIME() function instead of the ROUND() function. Say A1 and A2 contain:

0.6634606481481
0.6634606481500

and B1 and B2 contain:

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
=TIME(HOUR(A2),MINUTE(A2),SECOND(A2))

The formula results are:

0.663460648148
0.663460648148

These may be "close enough" for VLOOKUP or MATCH