Examples of SharePoint Calculated Field Formulas

SharePoint calculated field is a SharePoint column where you can implement different types of formulas, such as conditional formulas, date and time formulas, mathematical formulas and text formulas.

These formulas have its own use. Conditional formulas are use to test the condition of a statement and return a Yes or No value. Date and time formulas are use to to perform calculations that are based on dates and times. Mathematical formulas are use to perform a variety of mathematical calculations, such as adding, subtracting, multiplying, and dividing numbers. Text formulas are use to manipulate text, such as combining or concatenating the values from multiple columns, comparing the contents of columns, removing characters or spaces, and repeating characters.

Below are sample formulas on how to:

1. convert the datetime field value into different formats.


• Month-Year (e.g. January-2013)

=CHOOSE(MONTH([ValidityDate]),"January","February","March","April","May","June","July","August","September","October","November","December")&"-"&YEAR([ValidityDate])


• Year (e.g. 2013)

=TEXT(YEAR([ValidityDate]),"0000")


• Month (e.g. 01-January)

=TEXT(MONTH([ValidityDate]),"00")&"-"&CHOOSE(MONTH([ValidityDate]),"January","February","March","April","May","June","July","August","September","October","November","December")






get the start and end day of the month of the datetime field value


• start day of the month

=DATE(YEAR([ValidityDate]),MONTH([ValidityDate]),1)


• end day of the month

=DATE(YEAR([ValidityDate]),MONTH([ValidityDate])+1,1)-1

To see more examples of common formulas, click here.