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

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate how to return the value if the date is within range in excel. When delivering products or submitting important projects, it is essential to set a return value within a certain date. It clears out if the employee or student is doing his duties with attention or not. So, it is important to learn this process.


Download Practice Workbook

You can download the practice workbook from here.

Return Value Within Range.xlsx

5 Easy Methods to Return Value If Date Is Within a Range in Excel

If you follow the steps correctly, you should learn how to return the value if the date is within range in the excel cell on your own. The methods are:


1. Using VLOOKUP Function

In this case, our goal is to return the value if the date is within range by using the VLOOKUP Function. VLOOKUP Function is mainly used when you have to find a table or some info or any data by row. The steps of this method are.

Steps:

  • To begin with, arrange a dataset like the below image.

return value if date within range

  • In addition, insert the following formula in cell D15.
=VLOOKUP(D14,B5:F12,5,TRUE)

Inserting Formula to Return Value If Date Within a Range

  • Finally, you will get the below result.


2. Use of INDEX Function

We aim to return the value if the date is within the range by using the INDEX function. The INDEX function returns a value within a range. We have to follow the below steps to learn this whole method.

Steps:

  • First, arrange a dataset like the below image.

  • Next, insert the following formula in cell C15.
=INDEX(F5:F12,MATCH(1,IF(B5:B12>B15,IF(B5:B12<B16,1)),0))

Inserting Formula to Return Value If Date Within a Range

  • Finally, you will get the below result.


3. Applying Nested IF Function

Now, our goal is to return the value if the date is within the range by using the Nested IF Function. The NESTED IF Function gives a logical comparison within the proper date range. We have to follow the below steps to learn this whole method.

Steps:

  • At first, arrange a dataset like the below image.

Dataset to Return Value If Date Within a Range

  • Second, insert the following formula in cell E5.
=IF(D5>=B5,IF(D5<=C5,"In Range","Out of Range"),"Out of Range")

Inserting Formula to Return Value If Date Within a Range

  • Third, you will get the result for the cell and then use the Fill Handle option.

  • Last, you will get the below result.

Showing Result to Return Value If Date Within a Range


4. Combining IF & AND Functions

We aim to return the value if the date is within the range by using the combination of both IF and AND Functions. The If function gives logical comparison, and the And function returns if the condition is true or false.  We have to follow the below steps to learn this whole method.

Steps:

  • First, arrange a dataset like the below image.

  • Next, insert the following formula in cell E5.
=IF(AND(D5>=B5,D5<=C5),"In Range","Out of Range")

Inserting Formula to Return Value If Date Within a Range

  • After that, you will get the result for the cell and then use the Fill Handle option.

  • Finally, you will get the below result.

🔎 How Does the Formula Work?

  • AND(D5>=B5, D5<=C5): This portion presents the selected cell to the conditions that will be applied.
  • IF(AND(D5>=B5, D5<=C5), “In Range”, “Out of Range”): This portion presents the conditions of the function.

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


5. Utilizing LOOKUP Function

In this case, our goal is to return the value if the date is within range by using the LOOKUP Function. The LOOKUP Function is mainly used when you have to find a table or some info or any data by row. We have to follow the below steps to learn this whole method.

Steps:

  • To begin with, arrange a dataset like the below image.

  • In addition, insert the following formula in cell D15.
=LOOKUP(2,1/($B$5:$B$12>=C15)/($D$5:$D$12<=C16),$C$5:$C$12)

Inserting Formula to Return Value If Date Within a Range

  • Finally, you will get the below result.


How to Compare Dates in Excel

In this case, our goal is to return the value if the date is within the range using the INT Function. This function compares two certain data and gives results accordingly. We have to follow the below steps to learn this whole method.

Steps:

  • First, arrange a dataset like the below image and insert the following formula in cell D5.
=INT(B5)=INT(C5)

Inserting Formula to Return Value If Date Within a Range

  • Next, you will get the below result.


Things to Remember

  • If there remains any error in any formula, excel will show an error.
  • While using the formulas, you should give proper attention to the cell you are using. Otherwise, the formula won’t give the desired result.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to return the value if the date is within range in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Related Articles

Zehad Rian Jim
Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo