Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using SharePoint Calculated Fields


By:   |   Read Comments   |   Related Tips: > Sharepoint

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


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.
employees

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.
create column

Follow the three steps exactly as described and shown in the screenshot below.

  • Select "Calculated Column" as type of the field.
x employees
  • 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.
number
  • Select "Number" as the data type of the calculated field
data type

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.

all items
Next Steps
  • Use Calculated field to do various financial calculations.
  • Use Calculated columns to perform date and textual operations.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools