Learn 5 Ways to Use Dates in Formulas and Functions

Imagine you want to use this Excel Date “02/09/06” in a formula or function; the tendency is to use it directly until we receive errors.

You will be presented here with an example that uses a VLOOKUP function using a Date as the lookup_value argument.

Let’s see…

Imagine you want to look up “02/09/06” in a list located in A2:E22 range. Here you have the 5 methods you can employ…

1) Use Excel Date Directly from a Cell

Assuming the data is in G2, the formula would be this: =VLOOKUP(G2,$A$2:$E$22,3,0)

2) Insert a Text-Date with DATEVALUE function

    • Date Typed Directly in Function

=VLOOKUP(DATEVALUE(“02/09/06”),$A$2:$E$22,3,0)

    • Date stored as text in a cell

Assuming G2 is the date stored as text.

=VLOOKUP(DATEVALUE(G2),$A$2:$E$22,3,0)

The unique way to use a date (02/09/06 0r 02-09-06 notation) directly in a function/formula is by using DATEVALUE function.

You have two cases:

3) Insert “02/09/06” as Plain Serial Number

  • For 1900 date system: from 1 to 2,958,465
  • For 1904 date system: from 1 to 2,957,003

You can enter directly the serial number in any formula or function, let’s see…

=VLOOKUP(38757,$A$2:$E$22,3,0)

Where 38757 corresponds to 02/09/06

Important notice about serial numbers:

Of course the number must be on range in the Excel date-time code

4) Enter “02/09/06” with DATE Function

The DATE function syntax is as follows: DATE(year,month,day) so the formula would be this one:

=VLOOKUP(DATE(2006,2,9),$A$2:$E$22,3,0)

5) Enter Current Day with TODAY function

Insert the present day with TODAY function, see below…=VLOOKUP(TODAY(),$A$2:$E$22,3,0)