Excel If Date Is Between Range Then Return Value

Get FREE Advanced Excel Exercises with Solutions!

In this post, we examined 2 simple ways to find value in between date range in Excel and return it. We have shown them with proper guidance and clear illustrations. So, stick with us and follow the processes.


How to Return Value If Date Is Between Range in Excel: 2 Easy Ways

The graphic below illustrates a dataset including the delivery and acceptance dates of textile items from a specific order. In the following sections, we will go through several short Excel techniques that will return a value if the date falls within two ranges.

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


1. Check If Date Is Between Range and Return Yes or No

In the first technique, we simply try to determine whether a date falls between two certain dates or not. For that purpose, we utilized the IF function to get the return.

📌 Steps:

  • When an Excel worksheet contains dates in text format that you wish to filter, sort, or format as dates, or utilize in date calculations, the DATEVALUE function comes in handy.
  • DATEVALUE(“8/3/2022”) returns a date that can be used in Excel formula as a date otherwise Excel will recognize it as a text.
  • An IF statement can have two results. The first result is if your comparison is True, and the second is if your comparison is False.
  • Now here is the Excel formula that can find that the first column is between August 03, 2022, and August 08, 2022.
=IF(AND(B5>DATEVALUE("8/3/2022"), B5<DATEVALUE("8/8/2022")), "Yes", "No")

Utilizing the IF function

  • All is done without dragging. Now we will drag down the fill handle or just double-click on that.

drag down the fill handle


2. Look up Between Two Dates and Return Corresponding Value

In the second method, we simply try to determine whether a date falls between two certain dates, moreover with their corresponding data. For that purpose, we apply the LOOKUP function to get the return.

📌 Steps:

The LOOKUP function vector form syntax has the following ways to proceed:

  • lookup value indicates that LOOKUP will find the first vector. Lookup values can be numbers, logical values, names, or references to values.
  • lookup vector only indicates one row or column. Lookup vector values can be text, integers, or logical values.
  • result vector: A range that only has one row or column. The size of the result vector parameter must be the same as the size of the lookup vector argument. It must be of the same size

In this method, we will try to find customers against a particular date that is described in the first column of the dataset.

=LOOKUP(2,1/($B$5:$B$16<K5)/($C$5:$C$16>K5),$D$5:$D$16)

Applying LOOKUP function

  • Now we’ll drag down the fill handle, or just double-click on it.

Download Practice Workbook

You can download the practice workbook from the following download button.


Conclusion

Try to emulate these methods and steps to see if the date is between the ranges or return values corresponding to a particular date that is between two dates. Moreover, you are welcome to download the workbook and use it for your individual exercise. If you have any comments, concerns, or suggestions, please leave them on the comment board.


<< Go Back to If Date | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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