How to create Calculated Columns in SharePoint to show differences between dates

Home > Blogs > SharePoint > How to create Calculated Columns in SharePoint to show differences between dates

How to create Calculated Columns in SharePoint to show differences between dates

Like This Blog 6 Spike Xavier
Added by October 21, 2013

In this blog I will create a Calculated Column in SharePoint and add it to my blog tasks list to indicate the number of days I am early or if I’m late with due dates.

I created some data for the month of January 2013 and I calculated the number of days my blogs were early. This was done by calculating the difference between the completedDate and the Due Date in days. If the completedDate was later than the Due Date, the blog is late and the string LATE is returned. This is a fantastic use of calculated columns.

Create the custom Datetime Column CompletedDate 

Create-the-custom-datetime-column-completedDate.png

I have added a new column to an out-of-the-box tasks list called CompletedDate and formatted it as date time and added some data to my tasks list. I made sure to add at least one date where the completedDate is later than the Due Date. I customized the default view to only show the relevant columns.(not shown)

Create the Calculated Column 

Create-the-Calculated-Column.png

In the ribbon I click Create Column. For this example I will use a list column.

Choose Calculated Column as the Data Type 

Choose-Calculated-Column-As-The-Data-Type.png

I call my column DaysEarly and choose calculated column as the data type. This opens up the Formula section.

Enter the Formula 

Enter-The-Formula.png

The formula is part typing and part clicking the column options on the right hand side. I have not had good luck typing column names. I type the formulas but when it’s time to insert the column name I always double-click the column name from the Insert Column box on the righ to place it into my formula. This particular formula prefixes the date difference calculation formula just in case I am late and puts in the string “LATE” if I am, otherwise it returns a number of days I am early.

The formula in its entirety is:

IF(ISERROR(DATEDIF(completedDate,[Due Date],”d”)),”LATE”,DATEDIF(completedDate,[Due Date],”d”))

The first part of the formula checks to see if my calculation will return an error and if it does, it will return the value of LATE as the column value, otherwise it will perform the calculation and return the number of days (“d”) between the two dates where the completedDate is earlier than the Due Date as a number.

Validate the Results 

Validate-The-Results.png

It all looks good. I can see the days early appearing and the one blog I entered as being late has the value of the DaysEarly column set as LATE.


Spike Xavier
SharePoint Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Creating Lists and Libraries in SharePoint

0 1332 4

In this SharePoint training video, we’re going to cover creating Lists and Libraries. Wherever you create your List or Library, wherever you want to create it, you want to be there. You want to be in a particular Site that’s in a Site Collection. If I wanted to add a List or Library to this … Continue reading Creating Lists and Libraries in SharePoint

Creating Users and Managing Passwords in Microsoft Office 365

0 721 3

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.   For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365    

JavaScript for C# Developers – September 24, 2014

0 495 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Write a Comment

See what people are saying...

    Share your thoughts...

    Please fill out the comment form below to post a reply.