Dataset Overview
The graphic below illustrates a dataset that we’ll use to demonstrate these methods. The dataset includes the delivery and acceptance dates of textile items from a specific order.
Method 1 – Check If a Date Falls Within a Range and Return “Yes” or “No”
In this first technique, we’ll determine whether a date falls between two specific dates. We’ll use the IF function to achieve this.
- Date Conversion:
- If your Excel worksheet contains dates in text format that you want to filter, sort, format as dates, or use in date calculations, the DATEVALUE function will be useful.
- For example, DATEVALUE(“8/3/2022”) returns a date that can be used in Excel formulas as an actual date. Otherwise, Excel would treat it as text.
- IF Statement:
- An IF statement has two possible outcomes:
- The first outcome occurs if your comparison is true.
- The second outcome occurs if your comparison is false.
- To find out if a date in the first column falls between August 3, 2022, and August 8, 2022, insert the following Excel formula:
- An IF statement has two possible outcomes:
=IF(AND(B5>DATEVALUE("8/3/2022"), B5<DATEVALUE("8/8/2022")), "Yes", "No")
- Copy this formula down the column or double-click the fill handle.
Method 2 – Look Up Values Between Two Dates and Return Corresponding Data
In the second method, we’ll not only check if a date falls within a range but also retrieve corresponding data associated with that date. We’ll use the LOOKUP function.
- LOOKUP Function:
- The LOOKUP function has a vector form syntax with the following components:
- Lookup value: Specifies the value to search for. LOOKUP will find the first vector where this value exists. It can be a number, logical value, name, or reference.
- Lookup vector: Refers to a single row or column containing values. These values can be text, integers, or logical values.
- Result vector: A range with only one row or column. The size of the result vector must match the size of the lookup vector.
- The LOOKUP function has a vector form syntax with the following components:
-
- To find customers associated with a particular date described in the first column of your dataset, insert this formula:
- Copy this formula down or double-click the fill handle.
Download Practice Workbook
You can download the practice workbook from here:
<< Go Back to If Date | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!