VLookUp and Times

Greetings everyone,

I am a bit stumped currently and could use some help on why my VLookUp is not working.

Right now I am working with 2 tables. 1 is a "Times" lookup table that is structured like this:

TimeValue	Time	TimeDesc
0	12:00:00 AM	Night
.000694444	12:00:01 AM	Night
.001388889	12:00:02 AM	Night

So on and so forth

The other table contains the data I am trying to sort through. It has 50+ columns, but the key column for this purpose is a column titled "IncidentDate" which contains data structured like this:

IncidentDate

1/1/22 12:00:00 AM

3/5/22 1:15:00 PM

So on and so forth.

I essentially want the VLookUp to be able to check the IncidentDate value against the Times lookup table and return the "Night" value. Right now I am using this formula:

Please Login or Register  to view this content.

This, however, is not returning any results. Analysis shows that I am getting what I would expect from each part of the formula but it will not find the match in the table.

Anyone have any pointers?

Thanks!

Try using the formula highlighted in the excel sheet using Time formula to extract Time from Incidentdate instead of Right().
When using Vlookup from Times table , First column should be the column with the lookup value.
S]o,we need to select only two columns(Time and TimeDesc).

Originally Posted by Suilenroc

=VLOOKUP(RIGHT(AX2,LEN(AX2)-FIND(".",AX2)+1),Times,3,FALSE)

I suspect that you have several problems.

First, presumably AX2 contains date and time.

Ostensibly, the better way to extract numeric time is MOD(AX2,1).

However, since you are using a match function (VLOOKUP), it would be prudent to use --TEXT(MOD(AX2,1),"h:m:s") to round to the exact binary approximation of the time.

The double-negate converts text to numeric time.

-----

Second, with that change, you should eliminate the column that you label "Timevalue".

Instead, the leftmost lookup column should be what you label "Time". I will call that column B.

However, I suspect that you build the "Time" column by entering 12:00:00 AM into B2, then starting in B3, entering formulas to increment by one second.

Those formulas should be of the form =--TEXT(B2+"0:0:1","h:m:s"), again to round to the exact binary approximation of the time. Format the result as Custom h:mm:ss AM/PM.

-----

Finally, if you simply want to distinguish between "night", "morning" and "afternoon" times of day -- your example is not complete enough for us to know -- instead of populating the "Times" table with 86400 entries (I presume), consider the following simplification.

Time	Desc
12:00 AM	Night
6:00 AM	Morning
12:00 PM	Afternoon
6:00 PM	Night

If you enter the leftmost times manually, they will be rounded to their exact binary approximation, by definition.

Then, your lookup formula becomes:

=VLOOKUP(--TEXT(MOD(AX2,1),"h:m:s"), Times, 2)

The missing 4th parameter defaults to TRUE to match the row with the largest time on the left that is less than or equal to the lookup time extracted from AX2.