How to add a date difference column to a SharePoint list or library
Learn how you can use the DATEDIF SharePoint function to calculate the difference between two dates in a SharePoint list or library.
Document Management almost always involve working with dates, such as start dates, end dates, due dates, etc.
And often, you might want to perform date calculations, for example, to find out how many days are left before a due date arrives, that is, calculate the difference between two dates.
You can create a calculated column in SharePoint to calculate date differences or use any other function from a list of available SharePoint functions to perform other types of calculations in SharePoint.
Creating a date difference column in SharePoint
To add a column to a SharePoint list or library to calculate the difference between two dates:
- Ensure that you have a SharePoint list or library available that contains two date columns for which you want to calculate the difference. Here you’ll use a SharePoint Tasks list that contains a Start Date and a Due Date column as an example, and add a calculated SharePoint list column to calculate the amount of days between the start date and the due date.
- Go to the SharePoint list or library to which you want to add a date difference column.
- On the Settings menu, click Create Column.
- On the Create Column page, type a Name for the column (for example Date Difference) and select Calculated (calculation based on other columns) as the Type.
- On the Create Column page under Additional Column Settings, type the following in the Formula text box:
and then double-click Start Date in the Insert Column list box to add the start date field to the formula. Click in the Formula text box at the end of the formula and type:
and then double-click Due Date in the Insert Column list box to add the due date field to the formula. Click in the Formula text box at the end of the formula and type:
The completed formula should look something like:
=DATEDIF([Start Date],[Due Date],"D")
You can read more about using the DATEDIF SharePoint function on Office Online.
- On the Create Column page under Additional Column Settings, select Number for the data type returned from the formula, select 0 for the number of decimal places, and click OK.
Now whenever you create a new task in the SharePoint Tasks list and fill out the start date and due date, the difference between the two dates is calculated. The result of this date difference calculation can be used in SharePoint Designer workflows.