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.
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
- Date stored as text in a cell
Assuming G2 is the date stored as text.
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…
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:
5) Enter Current Day with TODAY function
Insert the present day with TODAY function, see below…=VLOOKUP(TODAY(),$A$2:$E$22,3,0)