RSS Feed Is this your first time here? Get regular updates

SharePoint Calculated Column Cheat Sheet

Date:May 19th, 2011 Author: Tags: ,
Category: Calculated Columns, SharePoint Ideas Comments:13 ;

Calculated columns are a really useful feature of any SharePoint list.

Whether you want to automatically show the profit on a sale, or the financial year that a date falls in to, or tidy up sloppy user input by capitalising names.  There is a lot you can do it with a Calculated Column.

But remembering the formulas and functions needed is not always easy, especially before the first coffee of the morning.  I know there is a way to get rid of the decimal places on a number but is it TRUN, or TRUNC, or TRUNK – oh no, hang on, that last one is just to do with elephants!

So, over the years we’ve gathered together a quick reference of some of the most commonly used functions and formulas – stuck on a wall above the desk it saves a lot of time and head scratching.  We’ve found ourselves sending it to clients from time to time, and they seem to find it quite handy too.

So we decided to tidy it up – clean off the coffee stains and make a nice PDF of it – and make it available to download, free – yes that’s right, FREE!

So why not download our free Calculated Column Cheat Sheet and save yourself some time and headaches?  This easy 3 page reference sheet gives you a selection of commonly used calculated column formulas, along with a pretty thorough reference of most of the functions and operators you are likely to need in your day to day work.

Download, print, stick it on the wall, and we hope you find it as handy as we do!

Tags: ,

Did you enjoy this post? Get the next one by RSS or by email

13 Responses to “SharePoint Calculated Column Cheat Sheet”

  1. Shaka says:

    Thanks for your comprehensive reference list for calculated fields. I am not a guru and have had a difficult day trying to create a calculated list that checks contact birthdays. I have a column for birthdays and another for the current date. How do I get another column to check and compare the dates. In other words, how can I force SP to ignore the year and just look at the date and month.

    I tried by creating a new column to strip out the year (=TEXT(Birthday,”mmmm dd”). but cant seem to do the same for the current date. Your speedy help will be much appreciated.

  2. Ryan says:

    @Shaka – Sounds like you’re trying to use the “Fake Today Column” trick?

    As it says on the bottom of the cheat sheet – it doesn’t work and when you look at your list tomorrow it will still show todays date.

    http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/

  3. Jason Walker says:

    Hi There

    I am having an issue with a formula and I wondering if you could help please.

    I have successfully created a forula which creates a url to a ticket in our organisation – the URL shows up fine but we need to have this URL show up as a hyperlink.

    We previously had it set up this way in Sharepoint 2007 but when the site was migrated over to Sharepoint 2010 , this issue arose.

    Is there some syntax which I need to use within the formula or do you know if this is something else?

    Any help with this matter would be greatly appreciated.

    Many thanks and kind regards

    Jason Walker

  4. Stuart Pegg says:

    Hi Jason,

    It sounds like you were using one of Christophe’s Content Editor Web Parts in 2007 to unescape the HTML; which may have been lost in the upgrade to 2010. Here is a link to the last part of his blog series on the 2010 version:
    HTML Calculated Column Solutions for 2010 Part IV

    If you’re not sure about implementing this solution, you may wish to check back on our blog late next week, as we’re about to release something that’ll make this significantly easier.

  5. Danny says:

    The formula for calculating quarter is incorrect. However with slight modification it works correctly:

    =YEAR([Created])&” – Q”&INT((((MONTH([Created]))-1)/3)+1)

  6. Ryan says:

    Good spot Danny – sorry for late reply, pdf will be updated shortly!

  7. JoeFortran says:

    =IF(MONTH<4,"Q1",IF(MONTH<7,"Q2",IF(MONTH<10,"Q3","Q4")))
    If,Then,Else==ArtificalIntelligence.
    I put a man on the moon before you were born.
    What have you done?

  8. Ryan says:

    @JoeFortran

    Your formula isn’t correct: You’re not passing a field to the MONTH function. If you do it becomes a little bit longer than the other example.

    Did you get the man back from the moon? ;)

  9. RockStar says:

    What if the fiscal year starts in September and ends in August?

  10. Stuart Pegg says:

    In that instance I think you’re better off using the more straightforward CHOOSE function:

    =”Q”&CHOOSE(MONTH([Start Date]),2,2,3,3,3,4,4,4,1,1,1,2)

  11. RockStar says:

    Thank you Stuart! That worked perfectly! :)

  12. mayank says:

    please help.

    i want to calculate a field value such that if it is less then 0.5 it would be round up to 0.5 (ex. .3 would be .5, 1.3 would be 1.5, 2.1 would be 2.5) and if the value is greater then 1.5 ti would be round up to the next integer(ex. .6 would be 1, 1.7 would be 2, 2.6 would be 3)

    thanks in advance

  13. Ryan says:

    @mayank – You’re better asking this sort of detailed question over at the Microsoft support forums or http://sharepoint.stackexchange.com/

Leave a Reply

Anti-Spam Quiz:

We need to confirm your email address so we have just sent you an email.


Please click the link in the email and you will have immediate access to the download