Function Problems

How to Calculate Due Date Using Shipping or Invoice Date in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to calculate a payment due date by adding payment terms to either the shipping date or the invoice date, depending on which is available.

Product: Excel
Device & OS: not provided
Scenario: Calculating dynamic invoice or payment due dates in a structured Excel table based on conditional date fields.
Observed behavior: The formula needs to output a blank cell if payment terms are missing, use the invoice date if the shipping date is blank, or use the shipping date if available, then add the payment terms.

Before you start

Ensure your data is formatted as an Excel Table (Ctrl+T) so structured references work correctly, and verify your date columns are properly formatted as Dates.

Solution 1Recommended

Use a Nested IF Formula with Structured References

This solution uses a nested IF function to check for blank fields and dynamically select the correct starting date to add your payment terms to.

The IF function evaluates conditions logically. By nesting IF statements, we can prioritize the Shipping Date, fall back to the Invoice Date, and prevent errors by checking if the Payment Terms are empty first.

Format data as a table

Select your data range and press Ctrl+T to convert it into a structured Table. Make sure columns are named exactly 'Payment Terms', 'Shipping Date', and 'Date' (for Invoice Date).

Select the Due Date cell

Click on the first cell in your Due Date column where you want the calculation to appear.

Enter the nested IF formula

Type or paste the following formula: =IF([@[Payment Terms]]="","",IF([@[Shipping Date]]="",[@Date],[@[Shipping Date]])+[@[Payment Terms]])

Apply and format

Press Enter to apply the formula. Since it is a Table, it should automatically populate down the column. Ensure the Due Date column is formatted as a 'Short Date'.

Formula Logic: This formula guarantees that if the payment term is left blank, no false date will be generated, keeping your financial records clean.

WPS Office Solution

Calculate Dynamic Due Dates Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced nested IF functions and structured table references, allowing you to manage invoices, shipping dates, and payment terms effortlessly.

1. Open your invoice tracker in WPS: Launch WPS Spreadsheet and open your existing invoice or shipping document.
2. Insert the logical formula: Click the target cell in the Due Date column and input the nested IF formula provided above.
3. Format as Date: Right-click the column, select 'Format Cells', and choose 'Date' to display the calculated serial number as a readable date.

Seamless compatibility with Microsoft Excel (.xlsx) formats and table structures.Full support for logical functions like IF, nested IFs, and date arithmetic.Intuitive UI for creating and managing structured data tables.Free, lightweight, and fast to load.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my due date formula return a random number like 44200 instead of a date?

Excel calculates dates as serial numbers. If you see a number like 44200, simply right-click the cell, select 'Format Cells', and change the format to 'Date'.

Can I use standard cell references instead of structured table references?

Yes. If you aren't using an Excel Table, you can replace the structured references with standard cell references. For example, use =IF(C2="","",IF(B2="",A2,B2)+C2) where A2 is Invoice Date, B2 is Shipping Date, and C2 is Payment Terms.

What if I want to calculate business days only for the due date?

To add only business days, use the WORKDAY function. You would modify the formula to =IF([@[Payment Terms]]="","",WORKDAY(IF([@[Shipping Date]]="",[@Date],[@[Shipping Date]]),[@[Payment Terms]])).

Why does my formula return a #VALUE! error?

A #VALUE! error usually occurs if the Payment Terms, Shipping Date, or Invoice Date contain text strings or spaces instead of actual numbers and recognizable dates. Ensure all referenced cells contain numerical data.

Excel & Data Problems