Problem
SharePoint is a great adhoc tool for storing data. Sometimes it is necessary to make automated calculations based on user inputs. Let’s take a look at how to calculate the difference between two dates using SharePoint calculated columns.
Solution
SharePoint provides a Calculated Field feature which facilitates the automation of derived columns. You can have numeric calculations as well as manipulating text-based strings. This solution shows how to calculate the number of years between start and end dates of a list item.
- Create a sample list containing information that uses two date fields. One such list can be seen in the screenshot below.
Let’s see how to calculate the difference between the start date and end date columns. For that we need to define a calculated column.
- Click on Settings -> Create Column, and provide a relevant name for the column.
Follow the three steps exactly as described and shown in the screenshot below.
- Select “Calculated Column” as type of the field.
- We will use the date difference function, DATEDIF, with start date and end date to get number of years. Place the formula exactly as shown for desired results.
- Select “Number” as the data type of the calculated field
After implementing every step, press “OK” and go back to the library. Now you will be able to see the calculated column having results of the date differences between the start and end dates in years.
Next Steps
- Use Calculated field to do various financial calculations.
- Use Calculated columns to perform date and textual operations.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019