Others

How to Use IF and DATEDIF Formulas in SharePoint Calculated Columns

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to configure a SharePoint calculated column to display the number of days between a 'Start date' and 'Completed date' using IF and DATEDIF formulas, strictly when the progress status equals 'Completed'.

Product: SharePoint
Device & OS: not provided
Scenario: Tracking task progress in a SharePoint list and dynamically calculating the time taken to complete a task.
Observed behavior: The calculated column should output the elapsed days appended with the word 'days' for completed items, and display 'N/A' for any other progress status.

Before you start

Verify the exact names of your target columns in the SharePoint list. If your column names contain spaces (like 'Start date'), they must be enclosed in square brackets within the formula.

Solution 1Recommended

Apply the Nested IF and DATEDIF Formula

Create or modify your calculated column using a nested formula that evaluates the status text before executing the date difference calculation.

SharePoint calculated columns support many standard Excel functions. By nesting a DATEDIF function inside an IF statement, you can ensure that the calculation only runs when specific conditions are met, such as a task being marked as Completed.

Access List Settings

Navigate to your SharePoint list, click the gear icon in the top right corner, and select 'List settings'.

Create or Edit a Column

Scroll down to the 'Columns' section. Click 'Create column' to add a new one, or click on an existing calculated column to edit it.

Enter the Formula

In the 'Formula' text box, enter the following exact string: =IF(Progress="Completed",DATEDIF([Start date],[Completed date],"d")&" days","N/A")

Set the Data Type

Below the formula box, select 'Single line of text' for the data type returned from this formula. Click 'OK' to save your changes.

Regional Settings Tip: If you receive a syntax error upon saving, your SharePoint regional settings may require semicolons instead of commas. If so, change the formula to: =IF(Progress="Completed";DATEDIF([Start date];[Completed date];"d")&" days";"N/A")

Free Microsoft Office alternative

Manage Complex Project Data Seamlessly with WPS Office

While SharePoint handles list-based data online, managing extensive project timelines and advanced formula calculations is often faster in a dedicated spreadsheet. WPS Spreadsheet is a powerful, lightweight alternative to Microsoft Excel that perfectly supports IF, DATEDIF, and hundreds of other standard formulas for local and cloud data management.

1. Open Your Tracker: Launch WPS Spreadsheet and open your exported project data or create a new tracker.
2. Input the Formula: Select the target cell and type =IF(C2="Completed", DATEDIF(A2, B2, "d")&" days", "N/A").
3. Drag to Apply: Press Enter, then drag the fill handle down to apply the exact same logic across thousands of rows instantly.

Fully compatible with Microsoft Excel (.xlsx) files and standard formula syntaxes.Completely free and lightweight Office suite covering Spreadsheets, Writer, Presentation, and PDF.Familiar user interface guarantees a seamless migration with zero learning curve.Built-in robust cloud syncing for collaborating on project trackers anywhere.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my calculated column display '#NUM!' or '#VALUE!'?

This usually happens if the 'Start date' is later than the 'Completed date' in your list, as DATEDIF cannot calculate negative date differences. It can also occur if one of the date fields is left blank but the progress is marked as 'Completed'.

Can I return a blank cell instead of 'N/A'?

Yes. Simply replace the "N/A" at the end of the formula with two double quotes (""). The updated formula would be: =IF(Progress="Completed",DATEDIF([Start date],[Completed date],"d")&" days","").

How can I calculate the difference in months instead of days?

To calculate the elapsed months, change the unit argument in the DATEDIF function from "d" (days) to "m" (months). Update the appended text accordingly, for example: DATEDIF([Start date],[Completed date],"m")&" months".

Office Quick Solutions