Best way to match time values to the nearest second

[wilro85]

I have two data sets that I need to merge together using time as the criteria for matching them up.

Table one keeps the time in the format of:
Number of seconds past 00:00 Jan-1, 1900

Table two keeps the time in the format of:
Number of days past 00:00 Jan-1, 1900

These two can be put into the same unit by changing one by a factor of 86400. (number of seconds per day) However, I'm left with a large number of significant figures that cause vlookup to fail. I need to have exact matches because I am expecting table one to have more time records than that of table two.

So far the closest I've been able to do is:
=roundup("cellxy",5)
But I was only getting 3 out of 17 matches as opposed to when I had the time in HH:MM:SS format and manually matched them up and had 14 of 17 matches.

So I have this other train of thought:
While I can display the excel date (39314.46843) in a human format (8/20/07 11:14:32 AM), I can't figure out a way to keep the human format as just text. If I can covert to that format without having excel want to keep it as a decimal date than I can get signficantly more matchs with vlookup.

Or perhaps someone has a complete different way of approaching this problem?

Thanks,
wilro85

[NBVC]

Can you put the 86400 factor in your Vlookup formula?

e.g. =Vlookup(A1*86400,X1:Z100,2,0) or =Vlookup(A1/86400,X1:Z100,2,0) depending on which way you're going.

this way the full decimal value is taken into account when searching?

[wilro85]

It still has more sig figs than are needed. The 5th decimal place is about tenths of a second, 4 decimal places starts trimming full seconds. But its those last 5-9 decimal places that are causing the headache. I'm reading an article about floating decimals now. Going to see if doing that changes the situation at all.

[wilro85]

For those who are interested in this, I ran something like this:

I haven't cleaned this code up yet, but it works as I wanted.

Sub decmatch()
Application.ScreenUpdating = False
datalength = Cells(Rows.Count, 1).End(xlUp).Row 'STI data from access
impdatalength = Cells(Rows.Count, 18).End(xlUp).Row 'TX data from box
Dim dat As Variant

For o = 1 To impdatalength
dat = Cells(o, 18).Value
CDec (dat)
Cells(o, 17).Formula = "=Roundup(trunc(" & dat & ", 5),6)"
Next
Columns("q:q").Copy
Range("q1").PasteSpecial Paste:=xlValues


For i = 2 To datalength
dat = Cells(i, 2).Value / 86400 'convert time from seconds to days
CDec (dat)
Cells(i, 14).Formula = "=Roundup(trunc((" & dat & "), 5),6)"
Cells(i, 14).NumberFormat = "general"
Cells(i, 14).Copy
Cells(i, 14).PasteSpecial Paste:=xlValues
Cells(i, 15).Formula = "=vlookup(n" & i & ",q1:u" & impdatalength & ",5, false)"
Cells(i, 13).Formula = "=if(ISNA(o" & i & ")," & ",(o" & i & "))"

Next

Columns("M:M").Copy
Range("M1").PasteSpecial Paste:=xlValues
For i = 2 To datalength
If Cells(i, 13).Value = "0" Then
Cells(i, 13).Clear
End If
Next
Columns("o:o").Clear
Application.ScreenUpdating = True
End Sub