Can anyone color code a column with an if statement?

Question

Here is what I am trying to do.

I have a SharePoint 2010 list that I want to color code dates based on that it expires 364 days after the date entered.

Example:

If the date is older then 334 then it will turn yellow. If the date is older then 364 then it will turn red.

Can anyone please help me with this? I am an limited admin and can only do this with an if states... no SharePoint Designer.

Answer 1

You are asking for a No SharePoint Designer and SharePoint 2010 solution.

It can be done in a Calculated Column without the use of any other Webparts like documented on the blogs the other answers refer to.

The technique is documented at http://www.viewmaster365.com/#/How The cons of this approach are also documented.

But it ain't easy; Calculated Columns are terrible to debug, so make one typo and you're on a wild goose chase.

It also requires some basic understanding of Javascript. Again see http://www.viewmaster365.com/#/How for the explanation.

Comparing with Today and highlighting row

I suggest you start with a Task list (with default Due Date column) to get comfortable with this Formula. Remember to set the datatype to Number!

This Javascript code (executed on every Item row) will color the Row based on the Due Date; I have changed the Range to include your date range. You can edit both the CSS and Range arrays to your likings.

="<img src='/_layouts/images/blank.gif' onload=""{"
&"var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"                
&"  var SPday=new Date();"
&"  SPday.setFullYear("
&YEAR([Due Date])
&","
&MONTH([Due Date])-1
&","
&DAY([Due Date])
&");"
&"  var days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"
&"  if(days>-4100){"
&"    var Range=[-364,-334,-7,0,7,14,21];"
&"    var CSS=['salmon','lightCoral','Pink','lightGoldenrodYellow','lightGreen','mediumSeaGreen','limeGreen'];"
&"    for (i=0;i<Range.length;i++){Color=CSS[i];if(days<Range[i]){ break;}}"
&"    TR.style.backgroundColor=Color;"
&"    var text=document.createTextNode(days+' days '+((days<0)?'past':'left'));"
&"    this.parentNode.appendChild(text,this);"
&"  }"
&"}"">"

Remember, debugging Calculated Columns is a PITA; you will be pulling your hairs out regularly.... I am completly bold after working on this technique for the past year.

More Formulas you can built yourself step by step at: http://www.viewmaster365.com/#/Create/

Update - more compact formula and some different JavaScript techniques

I used this code in an App which also adds interactivity to Priority, Progress and Status Fields.

All using one Calculated Column Formula. But since the limitation is 4000 bytes I had to be creative with the JavaScript used to minify it as much as possible and at the same time not pollute the global scope.

This is a working (but less readable) Formula showing some other JavaScript techniques. The .innerHTML line might be interesting to change in the example above.

="<img src=/_layouts/images/blank.gif onload=""{"
&"var D=new Date(),T=this;while(T.tagName!='TR')T=T.parentNode;"
&"D.setFullYear("
&YEAR([Due Date])
&","
&MONTH([Due Date])-1
&","
&DAY([Due Date])
&");"
&"var C,R=[-7,-4,0,1],"
&"N=Math.round((D.getTime()-new Date().getTime())/864e5);"
&"if(N>-42165){"
&"for(C=0;C<R.length;C++){if(N<R[C])break}"
&"T.style.backgroundColor=['salmon','yellow','pink','lightgreen','none'][C];"
&"this.parentNode.innerHTML=N<0?-N+' days past':N+' days to go';"
&"}"
&"}"">"

• Removed the GIF single quotes because the Browser will add them up to the next space
• walk up the DOM instead of using fixed parentNodes (also great to get to the TABLE)
• Shorthand e5 notation replaces 00000 saving three bytes
• -42165 is SharePoints default date for a blank value
• break statement without enclosing {;} saves 3 bytes
• color array as a constant, no need for a separate variable
• this.parentNode.innerHTML will replace the HTML code of the img.
  Note: not handy when you are debugging your code with F12.. as it will replace your code :-)
• can be compressed more; but that would make it completely unreadable o:-)

Update 2 - editing Formulas in the SharePoint UI

Well, as mentioned before, it sucks.

I have code on this GitHub page which does a Formula check on every keypress.

It is a distilled version of the advanced Editor I have developed for myself (but I can't make the Pro version public as it can really do bad things to SP and I do not want to be swamped with support calls)