Method 1 – Use IF Statement Between Two Numbers in Excel
1.1. Use IF Statement Between Two Numbers Exclusively
- Declare the boundary numbers.
- We have taken 32 and 26 as Upper Limit and Lower Limit.
- Find out if the age is between this boundary condition.
- Write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>$C$16,C5<$C$15),"Yes","No")- Press Enter to see the result.
- This No result indicates that the value is not between the limits.
If the value of the C5 cell( Age of the employee) is greater than the C16 cell (Lower Limit) and less than the C15 cell (Upper Limit) it will show Yes. It will show No.
- Drag down the formula in the D6:D13 range with the Fill Handle option.
- Observe the result in the DÂ column.
- The age is between 26 and 32 exclusively, it is showing Yes.
- The result is showing No.
1.2. Use IF Statement Between Two Numbers Inclusively
- The following step, we will find out if the age is between this boundary condition with the Inclusive condition.
- Write down the following formula in the formula bar of the E5 cell:
=IF(AND(C5>=$C$16,C5<=$C$15),"Yes","No")- Press Enter to see the result.
- This Yes indicates that the value is between the limits.
If the value of the C5 cell( Age of the employee) is greater or equal to the C16 cell (Lower Limit) and less or equal to the C15 cell (Upper Limit) it will show Yes.
- Dag down the formula in the E6:E13 range with the Fill Handle option.
- You can observe the result in the EÂ column.
- If the age is between 26 and 32 inclusively, it is showing Yes.
The numbers between 26 and 32 are showing Yes. For the Inclusive option, 26 and 32 are showing Yes and for the Exclusive option, 26 and 32 are showing No.
Method 2 – Check If Value Is Between Maximum and Minimum Numbers
2.1. Consider Exclusive Result
- Declare the boundary numbers.
- We have taken 35,38, and 34 as Upper Limits.
- We have taken 28,27, and 26 as Lower Limits.
- We have to calculate the Upper Limit and Lower Limit.
- We will find out if the age is between the calculated boundary condition.
- Write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>MIN($C$16:$E$16),C5<MAX($C$15:$E$15)),"Yes","No")- Press Enter to see the result.
- This No result indicates that the value is not between the limits.
Using the MIN function we will get the Lower Limit of the C16:E16 range and by the MAX function, we will get the Upper Limit of the C15:E15 range. If the value of the C5 cell( Age of the employee) is greater than the calculated Lower Limit and less than the calculated Upper Limit, it will show Yes.
- Drag down the formula in the D6:D13 range with the Fill Handle option.
- Observe the result in the DÂ column.
- If the age is between the calculated upper and lower range exclusively;Â it is showing Yes.
2.2. Find Inclusive Result
- Find out if the age is between this boundary condition with the Inclusive condition.
- Write down the following formula in the E5 cell:
=IF(AND(C5>=MIN($C$16:$E$16),C5<=MAX($C$15:$E$15)),"Yes","No")- Press Enter to see the result.
- This Yes result indicates that the value is between the limits.
Using the MIN function we will get the Lower Limit of the C16:E16 range and by the MAX function, we will get the Upper Limit of the C15:E15 range. If the value of the C5 cell (Age of the employee) is equal or greater than the calculated Lower Limit and equal or less than the calculated Upper Limit, it will show Yes.
- Drag down the formula in the E6:E13 range with the Fill Handle option.
- Observe the result in the EÂ column.
- The age is between the calculated upper and lower range inclusively, it is showing Yes.
Method 3 – Insert IF Statement Between Two Dates in Excel
STEPS:
- Declare the boundary numbers.
- We have taken the 1st of January, 2022 and 31st of December, 2022 as Lower Limit and Upper Limit.
- Find out if the joining date is between this boundary condition.
- Write the following formula in the formula bar of the D5 cell:
=IF(AND(C5>=$C$16,C5<=$C$15),"Yes","No")- Press Enter to see the result.
If the value of the C5 cell( Joining Date of the employee) is greater or equal to the C16 cell (Lower Limit) and less or equal to the C15 cell (Upper Limit), it will show Yes.
- Drag down the formula in the D6:D13 range with the Fill Handle option.
- Observe the result in the DÂ column.
- The joining date is between 1st January and 31 December 2022 then it is showing Yes.
- The result is showing No.
Method 4 – Apply IF Statement to Check If Dates Are Within Some Specific Days
4.1. Within Next N Days
- Write down the following formula in the formula bar of the D5 cell:
=IF(AND(C5>TODAY(),C5<=TODAY()+7),"Upcoming Joining","Already Joined")- Press Enter to see the result.
- Today is 11th January 2023.
- The date is not between 11 to 18th January 2023, it is showing Already Joined.
- Drag down the formula in the D6:D13 range with the Fill Handle option.
- Observe the result in the DÂ column.
- If the joining date is between today and (today+7) days, it is showing Upcoming Joining.
- The result is showing Already Joined.
4.2. Within Last N Days
- Write down the following formula in the formula bar of the D5 cell:
=IF(AND(C5>TODAY(),C5<=TODAY()+7),"Upcoming Joining","Already Joined")- Press Enter to see the result.
- Today is 11th January 2023.
- The date is not between 11 to 4th January 2023, it is showing No.
- Drag down the formula in the D6:D13 range with the Fill Handle option.
- Observe the result in the DÂ column.
- If the joining date is between today and (today–7) days, it is showing Recently Joined.
- The result is showing No.
Download Practice Workbook
To practice by yourself, download the following workbook.
Related Articles
- How to Use Conditional Formatting If Statement Is Another Cell
- Find Sum If Cell Color Is Green in Excel
- How to Prepare IF Statement Contains Multiple Words in Excel
- Use Wildcard with If Statement in Excel
- How to Use Multiple IF Statements in Excel Data Validation
