Formula Errors

How to Calculate Different Due Dates Conditionally in Excel

Aamir Naveed Akram Sep 30, 2026 868 views

Question details

The user needs an Excel formula to dynamically calculate due dates based on a specific revision type and submission date, leaving the target cell blank if the required values are missing.

How to Calculate Different Due Dates Conditionally in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking project or document due dates where Revision 'A' gets 21 days added to the submission date, and subsequent revisions get 14 days.
Observed behavior: The due date cell must remain completely blank until both the revision and submission date cells are populated, and then correctly calculate the deadline.

Before you start

Ensure that the cells containing your submission dates are formatted properly as Dates, and the revision cells contain plain text (such as 'A', 'B', etc.).

Solution 1Recommended

Use the IF and ISBLANK Functions to Calculate Conditional Due Dates

This approach checks if either the revision cell or the submitted date cell is empty using OR and ISBLANK. If they are, it leaves the result blank; otherwise, it calculates the respective due date.

Using the ISBLANK function is the safest way to evaluate empty cells in Excel, as it reliably avoids the common #VALUE! error when mathematical operations attempt to add numbers to empty references.

Select the target due date cell

Click on the cell in your worksheet where you want the conditionally calculated due date to be displayed.

Enter the conditional IF formula

Type the formula: =IF(OR(ISBLANK($C30),ISBLANK($J30)),"",IF($C30="A",$J30+21,$J30+14)). Adjust the cell references C30 (revision) and J30 (submission date) to match your actual columns.

Format the output as a date

Press Enter to apply the formula. Right-click the cell, select 'Format Cells', choose 'Date' from the Number tab, and click OK to display the final deadline properly.

Use the IF and ISBLANK Functions to Calculate Conditional Due Dates

Formula Complete: The cell will now remain perfectly blank until you have filled in both the revision letter and the original submitted date.

Solution 2

Use the IF and AND Functions for Date Calculation

An alternative logical approach that directly checks if both required cells contain data (using the non-blank operator) before performing the date addition.

Manage Complex Dates in WPS

Calculate Project Due Dates Seamlessly with WPS Office

WPS Office Spreadsheet provides complete support for advanced logical functions like IF, OR, AND, and ISBLANK. You can easily automate your project due dates and maintain flawless compatibility with Microsoft Excel files.

1. Open your spreadsheet: Launch WPS Office and open your project tracking spreadsheet.
2. Input the logic formula: Click on the target due date cell and enter the nested IF formula.
3. Format as Date: Press Enter, then apply Date formatting directly from the quick access Number Format menu on the Home tab.

100% compatible with Microsoft Excel formulas and date serial formats.Built-in advanced logical function support for seamless project tracking.Free, lightweight, and processes heavy spreadsheet data smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my date formula return a 5-digit number instead of a date?

Spreadsheets store dates as sequential serial numbers for mathematical calculations. If you see a number like 44500, select the cell, right-click to choose 'Format Cells', and change the number category to 'Date'.

How can I calculate business days instead of regular calendar days?

To calculate business days (skipping weekends), use the WORKDAY function. Replace the addition part (e.g., `$J30+21`) with `WORKDAY($J30, 21)`. You can also specify a range of holidays as an optional third parameter.

How do I add a third condition for a different revision letter?

You can nest another IF statement into the false-path of the logic. For example: `IF($C30="A", $J30+21, IF($C30="B", $J30+14, $J30+7))` handles revisions A, B, and anything else respectively.

Excel & Data Problems