Vlookup on Dates and Times in Excel

Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of lookup.

This tutorial shows you how to accurately use a lookup function on dates/times. This is important because time can be very tricky in Excel and sometimes you get positive results mixed-in with hidden negative results.

The VLOOKUP() function will be used as the example but the important steps are the same for any lookup function or formula.

Sections:

Date/Time Lookup Problem

Steps to fix a Date/Time Lookup

Notes

Date/Time Lookup Problem

The problem is that dates and times in excel are stored as numbers and times are the decimal part of the number. (format any date as General to see this)

Since times are stored as decimals, it is easy to miss an error in them and, in fact, a couple numbers can be off and you will still get a time that looks like it should when you format it as a date.

Here are some examples:

Both dates/times look the same:

Change the format to General so we can see their number equivalents:

They still look the same!!!

Now, show a few more decimal places:

You can see they are not the same!

After the 8 in the first one, there is a zero, but there is a 3 after the 8 in the second one.

This is what causes very frustrating problems when performing lookups on dates/times and this is the problem that needs to be fixed.

Steps to fix a Date/Time Lookup

To make times work in lookups, we need to round them.

1. Insert a new column to the left of the dates/times.
   
2. Input =ROUND(B1,5) into cell A1:
   
   B1 is the starting cell for the list of dates.
3. Copy the formula down by selecting the first cell, A1 and double-clicking the quick-fill handle in the lower right hand of the cell.
   
4. Copy the new data.
   
5. Select all of the old data and then hit Alt + E + S + V and Enter (it's called paste special - values). Now, your old data will have been replaced with the new data and it will have been rounded to the 5th digit after the decimal point.
   Then, delete the new column that you created with the ROUND() function.
   This means that the date and time that you see will actually be the date and time that is stored.
   But, we aren't done yet!
6. The Vlookup function, or whichever lookup function you use, now needs to be adjusted to work with the rounded numbers.
   Enter a normal Vlookup function BUT put ROUND(lookup_value,5) in for the lookup_value argument.
   
   The ROUND part has been highlighted for you.
   Without the ROUND function, the VLOOKUP function would have looked like this, with only D4 in for the lookup_value:
   
   However, you must include the ROUND function or this will not work since we already rounded the date/time values in our list.
7. Now you have a working lookup on a table of dates and times.

If you want to test out the Vlookup with and without the ROUND function, try it on the 10:34 time; without ROUND, it returns "value 4" and with it, it returns "value 5".

Notes

Dates and times in Excel are often tricky to deal with. This tutorial focuses on cleaning date/time data so that you can use it in lookups. If you type all of your dates and times in Excel by hand, you probably won't need this tutorial; however, if you import lots of date/time data into Excel, this tutorial will be a life-saver!

Download the sample file attached to this tutorial to see the examples in Excel.