Tuesday, November 14, 2017

Color coding list row based on DUE DATE

Requirement was to Color Code the list row based on Due Date.

Agree that there are many ways to achieve this - JSLink, SPFx etc. But below is a quick and easy method of adding the HTML and JavaScript in a calculated field.

Adding HTML in calculated field is not going to be supported in the future by MS, however this method still works:



Create a calculated column and add the below code. Here "DueDate" is the date field.
By modifying the Range and CSS values - logic can be changed for various due dates.


 =IF(ISBLANK([DueDate]),"Missing Due date",
    "
    &"    var SPday=new Date();"
    &"    SPday.setFullYear("
    &YEAR([DueDate])
    &","
    &MONTH([DueDate])-1
    &","
    &DAY([DueDate])
    &");"
    &"    var Days=Math.round((SPday.getTime()-new Date().getTime())/86400000);"
    &"    var Range=[ -365*20 , -365*10 , -1 , 0 , 30 , 60 , 90 ];"
    &"    var CSS=['Pink','Pink','Pink','transparent','yellow','transparent','transparent'];"
    &"    for (var i=0;i
    &"    var TR=this;while(TR.tagName!='TR'){TR=TR.parentNode}"
    &"    TR.style.backgroundColor=Color;"
    &"    this.parentNode.innerHTML=((Days<0 due="" font="" ot="">
    &"}"">")

Reference - https://sharepoint.stackexchange.com/questions/151144/how-to-use-today-and-me-in-calculated-column/151147