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!
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.
@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/
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
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.
The formula for calculating quarter is incorrect. However with slight modification it works correctly:
=YEAR([Created])&” – Q”&INT((((MONTH([Created]))-1)/3)+1)
Good spot Danny – sorry for late reply, pdf will be updated shortly!
=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?
@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? 😉
What if the fiscal year starts in September and ends in August?
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)
Thank you Stuart! That worked perfectly! 🙂
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
@mayank – You’re better asking this sort of detailed question over at the Microsoft support forums or http://sharepoint.stackexchange.com/
Calculate age as at today by month | days
Also calculate DOB against an appointment date
I have two dates in my SP list – Start Date and End Date. I want to create a calculated column that will give me a financial year i.e. 2022/23 or 2021/22 – something like this. Financial year in the UK starts in April and ends in March. Do you have a formula for this?
Is the cheat sheet PDF still available? I filled out your form, and submitted it, but no file or link arrived.
Thank you
Hi Jeff – yes it is – I’ve emailed it to you.
Please send me the cheatsheet of calculated columns for Lists