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.

Read More: Return Expected Value If Date Is Between Two Dates in Excel


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Hello, I'm Amit, a BUET graduate with a passion for Excel. Currently, I work as an Excel & VBA Content Developer at ExcelDemy, contributing insightful articles to the blog. I enjoy sharing my knowledge with others and always look for opportunities to grow as an Excel expert. Feel free to reach out if you need assistance or want to discuss the latest trends in Excel. Let's excel together in the world of data manipulation and analysis!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo