How to create Calculated Columns in SharePoint to show differences between dates
How to create Calculated Columns in SharePoint to show differences between dates
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.

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)

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

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

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.

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
You May Also Like
A Simple Introduction to Cisco CML2
0 3703 0Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2
How to Build in a PSMethod to your PowerShell Code
0 68 0In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code
How to use the PowerShell ConvertFrom- CSV Cmdlet to Save Coding Time with PS Script
0 322 4In this video, PowerShell instructor Jason Yoder shows how to use the ConvertFrom-CSV PowerShell Cmdlet to easily convert standard CSV files into PowerShell objects and speed up coding time. For instructor-led PowerShell training classes, see our course schedule: Microsoft Windows PowerShell Training PowerShell ConvertFrom-CSV script used in this video. Download ConvertFrom-CSV PowerShell Script <# ╔══════════════════════════════════════════════════════════════════════════════╗ … Continue reading How to use the PowerShell ConvertFrom- CSV Cmdlet to Save Coding Time with PS Script
See what people are saying...