Excel Time and Date issues

[Jack3010]

Hi,

So im trying to do a LOOKUP or INDEX/MATCH against date and time values, ive combined them to produce the number that excel works in but it doesnt recognise them as the same number even though the lists are produced exactly the same way? I dont know what to do.

 

[ParamRay]

Have you tried changing the number format of the values to see if they match up precisely?


For example, I have the date/time value 31/01/2017 18:21:30 in two separate cells. When I switch both cells to "General" number format and increase the decimal places, one cell shows 42766.7649266204 whereas the other cells shows 42766.7649277778.


As they're not an exact match, a lookup won't work as desired.

 

[Jack3010]

I believe this to be the issue and to resolve this i use
ROUND("cell",2)*100 to give each value an exact integer. This worked for the majority of the times but a couple of times a day did not work.

 

[ParamRay]

If the lookup is on date/time combinations, it would be better to use the floor function on both values to round down to the nearest second:

= FLOOR(A1, TIME(0, 0, 1))