How to Fix an Excel IF Formula Based on UG or PS Conditions
Question details
The user wants to return different date calculations using EDATE based on whether a cell contains 'UG' or 'PS', while outputting a blank cell if the reference date cell is empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating conditional future dates based on specific text codes ('UG' or 'PS') while handling blank reference date cells to avoid errors.
- Observed behavior
- The user needs the formula to evaluate multiple conditions accurately, returning a specific future date based on the text code, or a blank value without resulting in a #VALUE error.
Ensure the cell containing the reference date is correctly formatted as a Date, and the cell containing the text codes 'UG' or 'PS' does not have hidden trailing spaces.
Use a Compact SWITCH and EDATE Formula
The most efficient way to handle multiple text conditions and date calculations is to combine the SWITCH, ISBLANK, and EDATE functions.
The SWITCH function evaluates a single value against a list of matches and returns the corresponding result. By nesting it within EDATE, you can dynamically change the number of months added to a date based on the 'UG' or 'PS' text.
Click on the cell where you want the calculated date to be displayed.
Type the formula: =IF(ISBLANK(B1),"",EDATE(B1,SWITCH(A1,"UG",6,"PS",8))) (Assuming A1 contains the text code and B1 contains the start date).
Press Enter. Select the cell again, right-click, choose 'Format Cells', and select your preferred 'Date' format to display the serial number correctly.
Use a Nested IF and EDATE Formula
If you are using an older version of Excel that does not support the SWITCH function, a nested IF formula is the standard alternative.
Easily Handle Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions like SWITCH, IF, and EDATE, allowing you to execute complex conditional date calculations exactly like Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
- 2. Start the formula: Click on the destination cell and type '=' to begin entering your formula.
- 3. Input the conditional logic: Type your formula, such as =IF(ISBLANK(B1),"",EDATE(B1,SWITCH(A1,"UG",6,"PS",8))).
- 4. Format as Date: Press Enter, then use the 'Format Cells' option in the Home tab to display the value as a Date.
Frequently Asked Questions
Why does my EDATE formula return a random number instead of a date?
Excel calculates dates as serial numbers. If your EDATE formula returns a number like 44927, you simply need to change the cell format to Date. Right-click the cell, select 'Format Cells', and choose a Date format.
What happens if cell A1 contains a text other than UG or PS?
In the provided formulas, if the text does not match 'UG' or 'PS', the formula evaluates to false or hits the default switch case, returning a blank cell ("") instead of an error.
Is the SWITCH function available in all Excel versions?
No, the SWITCH function was introduced in Excel 2019 and Microsoft 365. If you are using an older version, you must use the nested IF formula alternative.
Why does my IF formula return a #VALUE! error?
A #VALUE! error usually occurs if the start date cell contains text instead of a valid date, or if there are invisible spaces disrupting the evaluation. Double-check your data types and ensure the date cell is formatted correctly.
