How to Use IF and EDATE to Compare Expiry Dates in Excel
Question details
The user needs a formula to compare an existing expiry date with a calculated date (e.g., 24 months after a start date) and return the appropriate date based on business rules.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing expiry dates and ensuring they do not exceed a maximum allowed duration (like 24 months) from a manufacturing or starting date.
- Observed behavior
- The user requires a logical formula that returns a maximum of 24 months after the start date, or the actual expiry date if it occurs sooner.
Ensure your date columns are formatted as 'Date' rather than 'Text' so the EDATE function calculates accurately, and identify the cells containing your starting date and current expiry date.
Using IF and EDATE to Cap Expiry Dates
Combine the IF and EDATE functions to enforce a maximum validity period, returning the earlier of the two dates.
The EDATE function calculates a date a specific number of months before or after a given start date. By nesting EDATE inside an IF function, you can evaluate whether the actual expiry date is greater than your maximum allowed timeframe (e.g., 24 months).
Click on the cell where you want the final, calculated expiry date to appear.
Type =IF(H8>EDATE(B8,24),EDATE(B8,24),H8), assuming H8 is the current expiry date and B8 is the manufacturing or start date.
Hit the Enter key to apply the formula. If the result appears as a large serial number, right-click the cell, select 'Format Cells', and apply a Date format.
Click and drag the fill handle at the bottom-right corner of the cell to apply this formula to the rest of your data column.
Manage Complex Date Calculations in WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and logical functions, including IF and EDATE, allowing you to easily handle complex business rules for expiry dates and timelines without any hassle.
- 1. Open your worksheet: Launch WPS Spreadsheet and open the document containing your dates.
- 2. Enter the IF and EDATE formula: Select an empty cell and input =IF(H8>EDATE(B8,24),EDATE(B8,24),H8) to cap the date.
- 3. Format the cells: Right-click the result, select 'Format Cells', and choose your preferred Date format.
- 4. Apply to all rows: Double-click the fill handle to apply the calculation to your entire dataset.
Frequently Asked Questions
Why does my EDATE formula return a random number instead of a date?
In spreadsheet programs, dates are stored as sequential serial numbers. If your formula returns a number like 44927, simply select the cell, right-click, choose 'Format Cells', and change the category to 'Date'.
How do I subtract months using the EDATE function?
To calculate a date in the past, use a negative number for the months argument. For example, =EDATE(B8, -6) will return the date exactly 6 months before the date in cell B8.
Can I use the MIN function instead of IF and EDATE?
Yes, using the MIN function is a shorter alternative for this specific logic. You can use =MIN(H8, EDATE(B8, 24)) to return whichever date comes first, eliminating the need for the IF statement.
What if my start date cell is empty?
If the referenced start date cell (e.g., B8) is blank, EDATE will treat it as 0 (January 0, 1900). To avoid incorrect results, wrap your formula in another IF statement to check for blank cells, like =IF(ISBLANK(B8), "", MIN(H8, EDATE(B8, 24))).
