'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Check if a Date is Within Three Years in Excel
Question details
The user needs an Excel formula to determine if a specific date falls within a three-year range of a reference date or fiscal year, returning a Yes or No result.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing dates or fiscal periods to automate a YES/NO output based on whether the target date falls within a 36-month window of a specified reference date.
- Observed behavior
- The goal state is an automated spreadsheet where target dates are correctly evaluated against a three-year timeframe or custom fiscal year ending on September 30.
Ensure your date cells are properly formatted as Dates in your spreadsheet. Identify the cell containing your static reference date (e.g., A2) and the cell containing the target date you want to check (e.g., B2).
Use the EDATE and IF Functions for 36-Month Comparisons
The EDATE function calculates the exact date 36 months in the past, making it the most accurate method for standard date comparisons over a three-year period.
This approach uses EDATE to subtract exactly 36 months from your reference date, then uses the IF function to evaluate whether the target date is greater than or equal to that resulting date.
Click on the empty cell where you want the YES or NO result to appear (for example, cell D2).
Type the formula =IF(EDATE($A$2,-36)<=B2,"YES","NO") into the formula bar. Replace $A$2 with your fixed reference date cell and B2 with the date you are checking.
Press Enter on your keyboard to calculate the result. Click and drag the small square handle at the bottom-right corner of the cell to copy the formula down for your entire dataset.
Use the AND Function for Fiscal Year Constraints
When working with custom fiscal years (e.g., ending on September 30), you must evaluate both the month and the year to determine if it falls within the required timeframe.
Calculate Complex Dates Easily with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced date and logical functions including EDATE, IF, and AND. You can seamlessly manage fiscal year comparisons and automate your data workflows in a familiar, user-friendly interface.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your dates.
- 2. Apply the date formatting: Select your date columns, right-click, choose 'Format Cells', and ensure 'Date' format is applied.
- 3. Input the formula: Click the cell for your output and type =IF(EDATE($A$2,-36)<=B2,"YES","NO").
- 4. Calculate your results: Press Enter to execute the formula, then drag the fill handle to apply it across all rows.
Frequently Asked Questions
What does the EDATE function do in Excel formulas?
The EDATE function returns the serial number of a date that is a specific number of months before or after a starting date. For example, EDATE(A2, -36) calculates the exact date 36 months prior to the date in cell A2.
How do I check if a date is within exactly one year instead of three?
You can easily adjust the months parameter in the EDATE function. To check if a date is within one year (12 months), change the -36 in your formula to -12, making it =IF(EDATE($A$2,-12)<=B2,"YES","NO").
Why is my EDATE formula returning a strange number like 44000 instead of a date?
Spreadsheet software stores dates as sequential serial numbers for calculation purposes. If you see a raw number instead of a date, select the cell, navigate to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.
