Whats the record for the longest Calculated SharePoint column?

Well my longest to date is 304 characters.. I’m not quite sure what the SharePoint limit is, but here’s mine…

=IF(LEN(MONTH(Dated)/3)=1,TEXT(Dated,”mmm-yy”),IF(LEN((MONTH(DATE(YEAR(Dated),MONTH(Dated)+1,DAY(Dated))))/3)=1,TEXT(DATE(YEAR(Dated),MONTH(Dated)+1,DAY(Dated)),”mmm-yy”),IF(LEN((MONTH(DATE(YEAR(Dated),MONTH(Dated)+2,DAY(Dated))))/3)=1,TEXT(DATE(YEAR(Dated),MONTH(Dated)+2,DAY(Dated)),”mmm-yy”),”ERR”)))

“And what pray, does that monstrosity do”, I hear you ask…

The answer is, it simply works out which quarter of the companies financial year the item appears in. The list item has a column called Dated which is a date only field.

Our financial year runs 1st Oct to 30th Sept with each quarter ending Dec,Mar,Jun,Sep.

In order to work out which quarter the item is in, I’ve nested 3 IF’s together.. If you’re not familiar with how the =IF function works in a calculated column, then it breaks down like this..

=IF(jonnysapples > janesapples,”Jonny has more apples”,”Jane has more apples”)
=IF(SOMETHING TO TEST) , DO THIS FOR TRUE , DO THIS FOR FALSE)

So in this instance if the column named Jonnysapples does indeed have more apples in it, then the phrase “Jonny has more apples” will be displayed in the resulting column. If it doesn’t, then “Jane has more apples” will be displayed.

Excellent.. So now what we want to do, is replace the output statement for FALSE (“Jane has more apples“) with the next test to see if Jonny is unhappy.

e.g.

=IF(jonnysapples > janesapples,”Jonny has more apples”,IF(jonnysmoney>0,”Jonny doesn’t care as he can buy some more”,”Jonny is upset because Jane has more apples”))

So, working the logic through,if Jonny has more Apples, we’ll see “Jonny has more apples“, if he has less, but some money (After checking the money column), then we’ll see “Jonny doesn’t care as he can buy some more” and then finally, if Jonny has less apples and no money, We’ll see the final state of “Jonny is upset because Jane has more apples”

So now we’ve implemented a 3 state result. By nesting a further if statement we’re able to twist this solution to our means.

So now we need to look at what logic we’re actually implementing in each of the IF statements.If you extract the “Something to test” part out of the first statement you’ll see:-

LEN(MONTH(Dated)/3)=1

Now if you bear in mind that the months that represent our year quarter period ends are all a factor of 3, then it starts to make sense. (Sept – 9th Month, June, 6th Month etc) Take the numeric value for the month. if you divide it by 3 and get a whole number e.g. a result with a length of 1, then we’re in a month divisible by 3. if we get any form of decimal, then we’re not.

=IF(LEN(MONTH(Dated)/3)=1,TEXT(Dated,”mmm-yy”),”DO THIS FOR FALSE”)

So if the month can be divided by 3 without any decimals, we’ll output the Dated column, formatted to look like mmm-yy. If it can’t then we’ll step into the next if statement in place of “DO THIS FOR FALSE”.

Again, very similar logic, however this time, we’re going to add 1 month to the date in Created and test that..

IF(LEN((MONTH(DATE(YEAR(Dated),MONTH(Dated)+1,DAY(Dated))))/3)=1,

If that results in a whole number, then we’ll output the Created+1 month using TEXT(DATE(YEAR(Dated),MONTH(Dated)+1,DAY(Dated)),”mmm-yy”),

And if it doesn’t? Yep you guessed it, we step into the third and final IF statement which we know is going to be true because the 4 month will divide by 3 once again. However to keep the parser happy, we’ve got to have an option of something to output for False, so that’s where you’ll see “ERR” in the formulae.

If this has worked, all you’ll see in your calculated column is Sep-09,Dec-09,Mar-10,Jun-10 etc….

This solution might not work for your if you use a different financial year and quarters, but the principal is sound..

Regards

Paul.