How to Use EDATE for Date Eligibility Rules in Excel
Question details
The user needs to configure date criteria in Excel using the EDATE function to filter records based on specific time offsets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a roster or tracker to select individuals who meet past date criteria, such as checking in at least six months ago and completing a previous duty at least twelve months ago.
- Observed behavior
- Needs the correct logical operators and EDATE syntax with negative month offsets to accurately filter eligible dates against the current date.
Ensure your date columns are formatted as valid Excel dates rather than text, and that any reference cells containing your month offsets contain numeric values.
Use EDATE with a Negative Offset and the TODAY Function
Combine EDATE, TODAY, and a less-than-or-equal-to operator to filter dates that occurred a specific number of months ago.
The EDATE function calculates a date a specified number of months before or after a starting date. By using TODAY() as the start date and a negative number for the months, you can dynamically find past dates for eligibility thresholds.
Identify or create reference cells for your criteria. For example, enter 6 in a reference cell (e.g., Lists!L5) for a six-month check-in requirement, and 12 in another cell (e.g., Lists!L7) for a twelve-month duty requirement.
Use the formula (Room_Roster[Room Assign/Check-in Date]<=EDATE(TODAY(),-Lists!L5)) to check if the assign date is six months or more before today.
Use the formula (Room_Roster[Date BO Duty Completed]<=EDATE(TODAY(),-Lists!L7)) to verify if the previous duty was completed at least twelve months ago.
If using these in an IF statement or filter, combine them using the AND function or asterisk array logic. A blank condition for 'Selected Date' can automatically exclude those currently awaiting completion.
Calculate Dates and Eligibility Easily in WPS Spreadsheet
WPS Spreadsheet fully supports the EDATE and TODAY functions, making it simple to track eligibility, calculate deadlines, and manage complex rosters. It is highly compatible with Microsoft Excel formulas and file formats.
- 1. Open your spreadsheet: Launch WPS Office and open your roster or date tracker workbook.
- 2. Enter the EDATE formula: Select the target cell, type =EDATE(TODAY(), -6), and press Enter to dynamically find the date exactly six months prior to today.
- 3. Apply conditional formatting: Highlight your date column, go to the Home tab, click Conditional Formatting, and use the 'Less Than' rule with your EDATE formula to automatically highlight eligible individuals.
Frequently Asked Questions
What does the EDATE function do in Excel?
The EDATE function returns the serial number of the date that is the indicated number of months before or after a specified start date. For example, EDATE(A1, 3) adds exactly 3 months to the date in cell A1.
How do I calculate a date in the past using EDATE?
To calculate a past date, use a negative number for the months argument. For instance, EDATE(TODAY(), -6) will return the date exactly six months prior to the current date.
Why is my EDATE formula returning a strange number like 44200?
Excel stores dates as sequential serial numbers for calculations. If you see a 5-digit number instead of a date, select the cell, go to the Home tab, and change the Number Format from 'General' to 'Short Date'.
Can I use cell references for the months argument in EDATE?
Yes, you can reference another cell that contains a number. For example, EDATE(TODAY(), -Lists!L5) subtracts the number of months specified in cell L5 on the 'Lists' sheet.
