Excel If Date Is Between Range Then Return Value

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.


Download Practice Workbook

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


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

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

Read More: How to Return Value If Date Is Within a Range in Excel


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


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

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo