Using SharePoint Calculated Fields
Written By: Siddharth Mehta -- 10/14/2010 --
(1599) comments --
Categories: Configurations, Design, Features, MOSS 2007, SharePoint 2010, SharePoint Foundation 2010, WSS3
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.
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
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.
Follow the three steps exactly as described and shown in the screenshot
- Click on Settings -> Create Column, and provide a relevant name for the column.
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
- 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
- Use Calculated field to do various financial calculations.
- Use Calculated columns to perform date and textual operations.
- Return to
MSSharepointTips to read about other topics and ideas.
- Check out MSSQLTips.com for great
information about Microsoft SQL Server.