Add Optional Extension Days to a Date with an Excel Formula in WPS Office
Question details
The user needs an Excel formula that adds 14 days to a start date by default, adds an extra 7 days when an extension cell contains Yes, and remains blank when the start date is empty.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is creating a date tracking sheet and wants to automatically calculate a due date based on a start date and an optional extension flag.
- Observed behavior
- The formula should return a blank cell when no start date is entered, add 14 days normally, and add 21 days when the extension condition is Yes.
Make sure your dates in column E are real date values, and the Yes/No marker in column J is entered consistently. The formula uses UPPER, so YES, Yes, and yes are all treated the same.
Use an IF formula to add 14 days and an optional 7-day extension
Use this when J5 may contain Yes or No and E5 may be empty; the formula returns a blank, 14-day, or 21-day result automatically.
This formula first checks whether E5 is empty. If it is empty, it returns an empty string. If E5 has a date, it adds 14 days as the default and adds 7 more days only when J5 is YES, case-insensitively.
Click the cell where you want the calculated due date to appear, for example F5.
Type or paste =IF(E5="","",E5+14+7*(UPPER(J5)="YES")) into the formula bar and press Enter.
Drag the fill handle from F5 down the column to apply the formula to other rows.
Count the starting date as day 1 by using 13 instead of 14
Use this when your workflow counts the starting workday as day 1 rather than day 0.
Calculate Due Dates with an Excel-Compatible Date Formula in WPS Office
Use WPS Office Spreadsheets to enter the same Excel-compatible formula and automatically calculate a blank, 14-day, or 21-day date result when the optional extension condition is met.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xls file containing your start date and extension columns.
- 2. Select the result cell: Click the cell where the due date should appear, such as F5.
- 3. Enter the date formula: Type =IF(E5="","",E5+14+7*(UPPER(J5)="YES")) into the formula bar and press Enter.
- 4. Apply to other rows: Drag the fill handle down to copy the formula to the remaining date rows.
Frequently Asked Questions
Does this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports IF, UPPER, and date arithmetic, so you can enter the same formula in the formula bar after selecting the result cell.
What does the formula return when the start date is blank?
It returns an empty string, so the result cell appears blank because the IF condition E5="" is met first and prevents the date calculation.
Can I make the extension days check case-insensitive?
Yes. Wrapping J5 in UPPER means yes, Yes, and YES all add the extra 7 days.
What should I change if I want 10 default days instead of 14?
Replace 14 in the formula with 10, for example =IF(E5="","",E5+10+7*(UPPER(J5)="YES")).
Why would I use 13 instead of 14?
If your date convention counts the starting date as day 1, use E5+13 so the default due date is still the 14th calendar day including the start date.
