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
Creating Users and Managing Passwords in Microsoft Office 365
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
Subnetting a TCP/IP Network using the Magic Box Method
See our class schedule for complete Course Schedule Training. Classes are held in Phoenix, AZ and can be attended online from anywhere in the world with RemoteLive™. Instructor: Rick Trader Video Transcription: One of the things that we might have to do in our corporate network is to take a class of IP addresses and then subnet that into … Continue reading Subnetting a TCP/IP Network using the Magic Box Method
Detailed Forensic Investigation of Malware Infections – April 21, 2015
How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015
Write a Comment
See what people are saying...
Share your thoughts...
Clean, crisp, absolutely useful. Read all the blogs, teachings etc on Microsoft (ha ha) and others, still no luck. Within a minute of finding your blog…. problem solved. THANK YOU.
Example: In leave request Form
From Date: today
EndDate: Today
Total Leave Request should be equal with 1
and
FromDate: today
EndDate: Today+2
Total Leave Request should be equal with 3
how you calculate for this case?
I have a different problem, Im attempting to get duration between Start date and Close date, if the close date is not filled in, I need it to use “today’s date” so we can see how many days it has been open.
I believe you have to put brackets around your column name since it contains spaces [FUNDS RECEIVED DATE]
This formula is so awesome and it would help me so much! But it does not seem to work for me.
In my case my formula is the same as yours, but with different names, is the spacing the problem? Do I have to put [ ] before and after the term “Funds Received Date”?
IF(ISERROR(DATEDIF(FUNDS RECEIVED DATE,[START TIME],”d”)),”LATE”,DATEDIF(FUNDS RECEIVED DATE,[START TIME],”d”))
If you could help me that would be amazing! Thank you in advance.
Mariana
Extremely helpful, thanks!