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.
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.
- 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")
- All is done without dragging. Now we will drag down the fill handle or just double-click on that.
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.
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.
- Now we’ll drag down the fill handle, or just double-click on it.
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.