Use an IF and OR Date Formula in Excel for Dates Within Six Months
Question details
Create a formula that returns “yes” when the date in column A is within six months before or after the corresponding date in column B.
- Product
- Microsoft Excel spreadsheet formulas
- Device & OS
- not provided
- Scenario
- The user needs to compare two dates in the same row and identify dates that are no more than six calendar months apart.
- Observed behavior
- The desired result is “yes” if A1 is between six months before B1 and six months after B1; otherwise, the result should be blank.
Make sure the values in columns A and B are real spreadsheet dates rather than text. Enter the formula in the result column for the first row, then fill it down for the remaining records.
Use IF, AND, and EDATE to test a six-month date range
Use this formula when you need a blank-or-yes result based on whether the date in A1 falls within six calendar months of the date in B1.
EDATE calculates dates a specified number of whole months before or after a reference date. The AND function confirms that A1 is on or after the date six months before B1 and on or before the date six months after B1.
Click the cell where you want the result to appear, such as C1.
Type or paste =IF(AND(A1>=EDATE(B1,-6),A1<=EDATE(B1,6)),"yes","") and press Enter.
Select C1 and drag the fill handle down, or double-click the fill handle, to apply the formula to additional rows.
A cell stays blank when the date in column A is earlier than EDATE(B1,-6) or later than EDATE(B1,6).
Check six-month date ranges with WPS Spreadsheets
WPS Spreadsheets supports IF, AND, and EDATE formulas, so you can compare dates and return a clear result such as “yes” for records that fall within a six-month range.
- 1. Open the worksheet in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the dates.
- 2. Choose the output column: Click the first empty result cell, such as C1, next to the dates in columns A and B.
- 3. Enter the date-range formula: Enter =IF(AND(A1>=EDATE(B1,-6),A1<=EDATE(B1,6)),"yes","") and press Enter.
- 4. Apply the formula to all records: Drag the fill handle from the formula cell down the output column to calculate each row.
Frequently Asked Questions
What does the EDATE function do in this formula?
EDATE returns a date a specified number of months before or after another date. EDATE(B1,-6) gives the date six months before B1, while EDATE(B1,6) gives the date six months after B1.
Why does the formula use AND instead of OR?
The date in A1 must meet both limits at the same time: it must be on or after the lower boundary and on or before the upper boundary. Using OR would return “yes” for almost every date.
How can I return “no” instead of a blank cell?
Replace the final empty string in the formula with “no”: =IF(AND(A1>=EDATE(B1,-6),A1<=EDATE(B1,6)),"yes","no").
How do I compare dates within six months in WPS Spreadsheets?
Click a result cell and enter =IF(AND(A1>=EDATE(B1,-6),A1<=EDATE(B1,6)),"yes",""). WPS Spreadsheets supports these functions in .xlsx and .xls workbooks.
