How to Determine If a Date Is Within a Date Range in Excel (2 Methods)

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.

Sample Data to Show 2 Ways to Return Value If Date Is Between Range in Excel


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:
=IF(AND(B5>DATEVALUE("8/3/2022"), B5<DATEVALUE("8/8/2022")), "Yes", "No")

Utilizing the IF function

  • Copy this formula down the column or double-click the fill handle.

drag down 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.
    • To find customers associated with a particular date described in the first column of your dataset, insert this formula:
=LOOKUP(2,1/($B$5:$B$16<K5)/($C$5:$C$16>K5),$D$5:$D$16)

Applying LOOKUP function

  • 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!
Al Ikram Amit
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo