Date/Time Reference Issue

**jakesing** · 05-10-2012, 02:56 PM

Hi All,

This is a very strange issue I am having trouble solving. I pull in a large set of data from bloomberg that basically takes in a series of dates and times, and prices of a security at every particular time. I have a separate list of times that I want to use to reference data from the raw data extraction. This second list is made of a formula that looks up a time and adds today() to it to match the format of the data extraction. So for example, the raw data might spit out a time that looks like '5/10/2012 2:01:46 PM'. In order to reference appropriately, I've rounded each of these to the nearest 5 minutes by rounding by a factor of (24 x 12). So the data will be in the form of '5/10/2012 2:00:00 PM'.

The data I was to use as the reference let's say is just 2:00PM. So I take that, add today() to it, and it becomes the same '5/10/2012 2:00:00 PM' as the raw data. Now here is the quirk. Certain times of the day are able to be referenced, and others aren't. I've tried using Index formulas and Vlookups, it's the same issue regardless. For instance, I have an 11:30 AM data point that successfully references the raw data, but the 2pm example from above does not. Of course if I do a simple test to see if the cells values are equal (=a1=a2), it comes out as true. So the values are the same but I can't reference it. Can anyone suggest a solution?

Cheers,
Jake

**FDibbins** · 05-10-2012, 03:10 PM

if you can upload a sample, it may be easier to help you? (also, if you want to compare 2 values, try using exact()

**jakesing** · 05-10-2012, 03:14 PM

Sure - here is a sample. As you see in column H, the recreated dates/times don't consistently reference properly. The 11:30 works, the 2pm does not work, even though they were built the same way.