Match Date to a Range of Dates and Times in Excel

There are numerous tools at our disposal when it comes to extracting data. Excel is undoubtedly one of the best of them. If we want to manipulate the dates, we should look no further.

In the example below, we will show how to find a certain date in a range of different dates and times.

Match Date to a Range of Dates and Times

For our example, we will use the list of random dates and times, all in 2015, and different revenues that a company achieved for that date:

Table

Description automatically generated

The easiest way to find the revenue that we want is to use the VLOOKUP function. This function is usually used with numbers, but since dates in Excel are just numbers formatted differently, we can use it with ease to get desired results.

Since we have a date and time together at our table, we must use VLOOKUP in a smart way.

If we type in the date 3/26/2015 in cell E2 and input the VLOOKUP formula

in cell E3 to find this date in the table, we will be returned an error:

Graphical user interface, application, table, Excel

Description automatically generated

The reason for the error is the fact that we used 0, or FALSE, for range_lookup, which means we are searching for an exact match, and our date was formatted with a custom time, which will always be set to 00:00:00. To remove the error, we need to precise the time we search for, or we need to type in 1, or TRUE, for range_lookup value:

Graphical user interface, application, table, Excel

Description automatically generated

Match Date to a Range of Dates and Times Formatted as Text

This happens often in Excel when, for some reason, we have our dates formatted as Text. This can happen, for example, when exporting from CSV. We will use the same numbers for our example, and will notice an error returned when using VLOOKUP from the previous example:

Graphical user interface, application, table, Excel

Description automatically generated

To resolve this issue, we need to convert our lookup_value (in our case, cell K2) to TEXT. This is the VLOOKUP function variation that we need to insert:

This formula converts our lookup_value to text and it also omits all the values after that, by concatenating it with the asterisk. This is the result we get:

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.