Md. Araf Bin Jayed
Excel users can solve many problems by using some formulas or proper commands or by combining both in Excel. One such problem is checking if a particular month is between two dates in Excel. The solution is crucial for giving payments to employees or checking the time limit for completing a project. In this article, we will show you how to check if month is between two dates in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
2 Easy Ways to Check If Month Is Between Two Dates in Excel
In this article, you will see two different ways to check if a month is between two dates in Excel. Firstly, we will merge the IF function and the AND function to check the desired result. In our second procedure, we will combine multiple functions to check if a month is between two dates in Excel.
For our working procedure, we will use the following data set. Here in column B and in column C, we have the start date and end date of a certain period. In column D, we have the data for some particular months with specific dates. Our goal is to check if the months fall between those dates or not.
1. Merging IF and AND Functions to Check If Month Is Between Two Dates
In our first procedure, we will merge the IF function and the AND function to check if a month is between two dates. The AND function will first check if the month falls between the two dates. According to that condition, the IF function will show the final result. The detailed steps for this procedure are as follows.
Step 1:
- First of all, create another column in column E to show the result of this procedure.
Step 2:
- Secondly, in cell E5, type the following formula of the merged functions.
=IF(AND(D5>=B5,D5<=C5),"In Between","Not In Between")
Formula Breakdown
- AND(D5>=B5,D5<=C5): The AND function will check if the particular month of cell D5 is greater than or equal to the date in cell B5 and less then or equal to the date in cell C5. If both the conditions are true, then it will show True, otherwise False.
- IF(AND(D5>=B5,D5<=C5),”In Between”,”Not In Between”): If the statement from the previous step is true, the IF function will show In Between. Otherwise, it will show Not In Between.
Step 3:
- Thirdly, press Enter to get the desired result.
- Then, use AutoFill to drag the formula to the lower cells of the Result column.
Read More: Return Expected Value If Date Is Greater Than Today in Excel
2. Combining Multiple Functions to Check If Month Is Between Two Dates in Excel
For our second procedure, we will combine multiple functions to get our desired result. The functions used in this combination are- the IF function, the AND function, the MIN function, and the MAX function. Each function will play its role in determining the final result. Go through the following steps for a better understanding of this procedure.
Step 1:
- First of all, in cell E5 of the Result column, type the following combination formula.
=IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),"In Between",IF(D5<MIN(B5:C5),"Delayed","Over"))
Formula Breakdown
- AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)): Firstly, the AND function will check if the value of cell D5 is greater than or equal to the minimum value of cell range B5:C5 and less than or equal to the maximum value of the cell range B5:C5. If both the conditions are true, then it will show True, otherwise False.
- IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),”In Between”: Then, the IF function will check if the previous statement or condition is true or false. If the result is true, it will show “In Between” as a result.
- IF(AND(D5>=MIN(B5:C5),D5<=MAX(B5:C5)),”In Between”,IF(D5<MIN(B5:C5),”Not In Between”,”Over”)): Finally, if the previous statement is false, then the second IF function will check the value of cell D5 is less than the minimum value of cell range B5:C5 or not. If the result is true, it will show “Delayed” and if the result is false, it will show “Over”.
Step 2:
- Secondly, press Enter to see if the particular month is in the range of those two dates.
- Then, with the help of Fill Handle, determine results for all the lower months.
Read More: Return Expected Value If Date Is Between Two Dates in Excel
Conclusion
That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to check if a month is between two dates in Excel by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moment to solve your issues and we will reply to your queries with the best possible solutions ever.
