Others

How to Calculate Duration Between Custom Dates in Microsoft Project

Amos Gikunda Oct 20, 2026 868 views

Question details

The user needs a custom duration formula to calculate the time between two custom dates in Microsoft Project while handling 'NA' values to avoid formula errors.

How to Calculate Duration Between Custom Dates in Microsoft Project

Product: Microsoft Project 2019
Device & OS: not provided
Scenario: Creating a custom formula to calculate the duration between Date1 and Date2, and falling back to the current project date if Date2 is unavailable.
Observed behavior: The custom duration formula returns #ERROR when a custom date field contains 'NA' instead of a valid date.

Before you start

Ensure you have administrator or editor access to customize fields in your Microsoft Project 2019 file before modifying custom formulas.

Solution 1Recommended

Use an IIf Formula with ProjDateDiff to Handle NA Values

Apply a conditional formula that checks if the custom date fields contain valid Project dates to prevent the #ERROR output when fields contain NA.

Microsoft Project dates are stored as serial values (for example, values greater than 50000 are often used to represent 'NA'). By combining the IIf, IsDate, and ProjDateDiff functions, you can reliably calculate the project duration. If Date2 is NA, the formula intelligently defaults to the Project Current Date.

Open Custom Fields

Right-click on a column header in your Microsoft Project view and select 'Custom Fields' from the context menu.

Select the Task Field

Choose the specific Task field you want to calculate the duration for (e.g., Duration1).

Open the Formula Editor

Click the 'Formula...' button located under the Custom attributes section.

Input the Conditional Formula

Enter the following formula: IIf(([Date1]>50000 And [Date2]>50000) Or ([Date1]>50000 And IsDate([Date2])),0,IIf(IsDate([Date1]) And [Date2]>50000,ProjDateDiff([Date1],[Current Date])/480+1,ProjDateDiff([Date1],[Date2])/480+1))

Save and Apply

Click 'OK' to save your formula and apply it to your project data.

Use an IIf Formula with ProjDateDiff to Handle NA Values

Formula Precision: Using the [Current Date] field instead of Now() is highly recommended if you do not require minute-level precision in your duration calculations.

Free Microsoft Office alternative

Track Project Timelines Easily with WPS Office

Microsoft Project formulas can be complex and error-prone. As a free, lightweight, and easy-to-use alternative, WPS Spreadsheet lets you track project durations, handle dates, and manage tasks with seamless Microsoft Excel format compatibility.

1. Download WPS Office: Install WPS Office for free from the official website to access WPS Spreadsheet.
2. Open WPS Spreadsheet: Create a new spreadsheet or open your existing project timeline document.
3. Use Basic Date Formulas: Use standard Excel-compatible formulas like =DATEDIF(A2, B2, "d") to calculate task durations without complex nested IIf statements.

100% compatible with Microsoft Excel (.xlsx) formats for easy sharing.Free, lightweight, and fast installation without the steep learning curve.Familiar UI with simple date formulas like DATEDIF for easy project tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Project show #ERROR in my custom duration field?

The #ERROR occurs when a custom formula encounters a non-date value, such as 'NA' (stored as a serial value greater than 50000 in Project), and attempts to process it using standard date functions like ProjDateDiff without prior validation.

What is the difference between [Current Date] and Now() in Project formulas?

The [Current Date] field returns the project's current status date without a specific time stamp, while Now() includes the current system time. Use [Current Date] unless you specifically need minute-level precision for your task durations.

How does the IsDate function work in MS Project custom fields?

The IsDate function evaluates whether a given field contains a valid date format. It is used in custom formulas to prevent calculation errors by verifying the data before executing duration functions like ProjDateDiff.

Office Quick Solutions