Vlookup problem when looking for a calulated time difference

Hi all,

I have run into a little problem that i hope you can help me with. In my attached workbook there are two sheets, "Time" and "Reference". In "Time" i have my data; col A is start time, col B is stop time col C is the difference between them, and in col D is the vlookup that refers to sheet "Reference". I want to find each difference in col C and take the correct value from sheet "Reference". I get an error. Can somebody help me explain why and how i can solve my problem?

/Masun

Hi,

Thank you for quick replay! Why dont i get a hit when i want to use exakt match?

/Masun

Hi,

Yeah i changed that. But howcome it dosent work with an exact match? Any ideas?

Hi,

But how can the values be different? Donīt understand that. I calculated one of them and its just a matter of hours and minutes, the other i just autofilled manually, how can there be different values? 7 hours and 11 minutes would be the same value regardless if its calculated or manually added?

Times are stored internally by Excel as fractions of a 24-hour day, so 12 noon has the value 0.5, 6:00am is 0.25, 6:00pm is 0.75, and so on. Now, as these are fractional values, they cannot be stored with complete accuracy, as there is a limit to the number of bytes used for storing fractions. It also turns out that some (decimal) fractions cannot be represented in binary (the number type used by computers), in the same way that 1/3 cannot be expressed completely with decimal fractions.

Consequently, there will be errors introduced when dealing with times. These errors may be very small (fractions of milliseconds), but they will prevent exact matches of data when you expect there to be a match. You can use MROUND to ensure that the lookup value will be a multiple of a minute, e.g. by doing this:

=VLOOKUP(MROUND(C2,1/24/60),Reference!$A$1:$B$6169,2,0)

but that will not always find an exact match as the values in column A of the Reference sheet have not similarly been rounded.

Another way is to convert your times to text. Put this formula in C1 of the Reference sheet:

=TEXT(A1,"[h]:mm:ss")

and copy this down to the bottom. Then copy column B into column D. Then you can use this formula in D2 of the Time sheet:

=VLOOKUP(TEXT(C2,"[h]:mm:ss"),Reference!$C$1:$D$6169,2,FALSE)

to find the exact matches.

Hope this helps.

Pete

Pete,

Thanks for your answer! I will try your solution first thing tomorrow. I am sure it will work. Many thanks!

Thanks for the explanation. I am not familiar with that binary stuff...but for a noob like me it seems like excel should use the same fractional value when storing 7:11, the number of bytes should be the same. But now i know that it doesnt, and i dont think i understand nor need to understand why it is like this. The knowledge of that it will be problems when dealing with time is enough. Once again, thank you!

/Masun

You're welcome - thanks for the rep.

Pete