SOLVED

Color Code SharePoint List cell based on Due Date and Status columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2206481%22%20slang%3D%22en-US%22%3EColor%20Code%20SharePoint%20List%20cell%20based%20on%20Due%20Date%20and%20Status%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2206481%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20Javascript%20(at%20the%20bottom)%20on%20my%20SP%202013%20site%20to%20color%20code%20a%20Due%20Date%20field.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20code%20as%20written%2C%20does%20just%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20also%20need%20to%20remove%20any%20color%20from%20the%20Due%20Date%20is%20the%20Status%20column%20for%20the%20list%20item%20is%20set%20to%20Completed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20code%20below%2C%20where%20it%20says%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eif%20(MyDateVar%20%26lt%3B%3D%20today)%20%7B%0Areturn%20%22%3CDIV%20style%3D%22background-color%3Ared%3Bcolor%3Awhite%22%3E%22%20%2B%20ctx.CurrentItem.DueDate%20%2B%20%22%3C%2FDIV%3E%22%3B%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20thinking%20of%20adding%20%7C%7C%20%5Bstatus%5D%20%3D%20%22Completed%22%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20...but%20it%20does%20not%20work.%20I%20have%20zero%20knowledge%20of%20javascript%26amp%3Bcolon%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eif%20(MyDateVar%20%26lt%3B%3D%20today%20%7C%7C%20%5Bstatus%5D%20%3D%20%22Completed%22)%20%7B%0Areturn%20%22%3CDIV%20style%3D%22background-color%3Ared%3Bcolor%3Awhite%22%3E%22%20%2B%20ctx.CurrentItem.DueDate%20%2B%20%22%3C%2FDIV%3E%22%3B%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20can%20I%20do%20to%20not%20add%20color%20to%20the%20%3CSTRONG%3EDue%20Date%3C%2FSTRONG%3E%20field%20%3CSTRONG%3Eif%20there%20is%20no%20Due%20Date%3C%2FSTRONG%3E%20entered%20or%20if%20the%20%3CSTRONG%3EStatus%3C%2FSTRONG%3E%20column%20is%20set%20to%20%3CSTRONG%3ECompleted%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20below%20code%20works%20for%20setting%20the%20Due%20Date%20colors%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3CSCRIPT%20type%3D%22text%2Fjavascript%22%3E%20%20%0ASP.SOD.executeFunc(%22clienttemplates.js%22%2C%20%22SPClientTemplates%22%2C%20%0Afunction%20()%20%7B%0A%20%20%20%20var%20statusFieldCtx%20%3D%20%7B%7D%3B%0A%0A%20%20%20%20statusFieldCtx.Templates%20%3D%20%7B%7D%3B%0A%20%20%20%20statusFieldCtx.Templates.Fields%20%3D%20%7B%0A%20%20%20%20%2F%2Finternal%20column%20name%20between%20double%20quotes%20on%20the%20next%20line.%20If%20your%20column%20name%20has%20a%20space%20in%20its%20internal%20name%20use%20%22_x0020_%22%20in%20place%20of%20the%20space.%20%0A%20%20%20%20%20%20%20%20%22DueDate%22%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%22View%22%3A%20ColorCodeDueDate%0A%20%20%20%20%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%3B%0A%0A%20%20%20%20SPClientTemplates.TemplateManager.RegisterTemplateOverrides(statusFieldCtx)%3B%0A%7D)()%3B%0A%0Afunction%20ColorCodeDueDate(ctx)%20%7B%0A%20%20%20%20%2F%2Fupdate%20internal%20column%20name%20at%20the%20end%20of%20the%20next%20line%0A%20%20%20%20var%20MyDateVar%20%3D%20new%20Date(ctx.CurrentItem.DueDate)%3B%0A%20%20%20%20MyDateVar.setHours(0%2C0%2C0%2C0)%3B%0A%20%20%20%0A%20%20%20%20var%20today%20%3D%20new%20Date()%3B%0A%20%20%20%20today.setHours(0%2C0%2C0%2C0)%3B%0A%0A%20%20%20%20var%20then1%20%3D%20new%20Date()%3B%0A%20%20%20%20%2F%2Fuse%20this%20to%20set%20how%20many%20days%20you%20want%20var%20then%20to%20be.%0A%20%20%20%20then1.setHours(0%2C0%2C0%2C0)%3B%0A%20%20%20%20then1.setDate(then1.getDate()%20%2B%201)%3B%0A%0A%20%20%20%20var%20then14%20%3D%20new%20Date()%3B%0A%20%20%20%20then14.setHours(0%2C0%2C0%2C0)%3B%0A%20%20%20%20%2F%2Fuse%20this%20to%20set%20how%20many%20days%20you%20want%20var%20then%20to%20be.%0A%20%20%20%20then14.setDate(then14.getDate()%20%2B%2014)%3B%0A%0A%20%20%20%20var%20then15%20%3D%20new%20Date()%3B%0A%20%20%20%20then15.setHours(0%2C0%2C0%2C0)%3B%0A%20%20%20%20%2F%2Fuse%20this%20to%20set%20how%20many%20days%20you%20want%20var%20then%20to%20be.%0A%20%20%20%20then15.setDate(then15.getDate()%20%2B%2015)%3B%0A%0A%20%20%20%20%2F%2F%20if%20there's%20no%20due%20date%20don't%20render%20anything.%20Also%20update%20the%20column%20names%20in%20the%20else%20statements%20below.%20If%20your%20column%20name%20has%20a%20space%20in%20its%20internal%20name%20use%20%22_x0020_%22%20in%20place%20of%20the%20space.%0A%20%20%20%20if%20(MyDateVar%20%26amp%3Bamp%3Bamp%3Bamp%3Blt%3B%3D%20today)%20%7B%0A%20%20%20%20%20%20%20%20return%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3Bdiv%20style%3D'background-color%3Ared%3Bcolor%3Awhite'%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%20%2B%20ctx.CurrentItem.DueDate%20%2B%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3B%2Fdiv%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%3B%0A%20%20%20%20%7D%0A%20%20%20%20else%20if%20(MyDateVar.getTime()%20%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%3D%20then1.getTime()%20%26amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3B%26amp%3Bamp%3Bamp%3Bamp%3Bamp%3Bamp%3B%20MyDateVar.getTime()%20%26amp%3Bamp%3Bamp%3Bamp%3Blt%3B%3D%20then14.getTime())%20%7B%20%0A%20%20%20%20%20%20%20%20return%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3Bdiv%20style%3D'background-color%3Ayellow%3Bcolor%3Ablack'%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%20%2B%20ctx.CurrentItem.DueDate%20%2B%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3B%2Fdiv%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%3B%0A%20%20%20%20%7D%0A%20%20%20%20else%20if%20(MyDateVar%20%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%3D%20then15)%20%7B%20%20%0A%20%20%20%20%20%20%20%20return%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3Bdiv%20style%3D'background-color%3Agreen%3Bcolor%3Awhite'%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%20%2B%20ctx.CurrentItem.DueDate%20%2B%20%22%26amp%3Bamp%3Bamp%3Bamp%3Blt%3B%2Fdiv%26amp%3Bamp%3Bamp%3Bamp%3Bgt%3B%22%3B%0A%20%20%20%20%7D%0A%7D%0A%3C%2FSCRIPT%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2206481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EColor%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I am using the Javascript (at the bottom) on my SP 2013 site to color code a Due Date field.

 

This code as written, does just that.

 

However, if the Status column for the list item is set to Completed, then the Due Date field should not have color..

 

 

 

 

 

What can I do to not add color to the Due Date field if there is no Due Date entered or if the Status column is set to Completed.

 

 

The below code works for setting the Due Date colors:

 

 

 

<script type="text/javascript">  
SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", 
function () {
    var statusFieldCtx = {};

    statusFieldCtx.Templates = {};
    statusFieldCtx.Templates.Fields = {
    //internal column name between double quotes on the next line. If your column name has a space in its internal name use "_x0020_" in place of the space. 
        "DueDate": {
            "View": ColorCodeDueDate
            }
    };

    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(statusFieldCtx);
})();

function ColorCodeDueDate(ctx) {
    //update internal column name at the end of the next line
    var MyDateVar = new Date(ctx.CurrentItem.DueDate);
    MyDateVar.setHours(0,0,0,0);
   
    var today = new Date();
    today.setHours(0,0,0,0);

    var then1 = new Date();
    //use this to set how many days you want var then to be.
    then1.setHours(0,0,0,0);
    then1.setDate(then1.getDate() + 1);

    var then14 = new Date();
    then14.setHours(0,0,0,0);
    //use this to set how many days you want var then to be.
    then14.setDate(then14.getDate() + 14);

    var then15 = new Date();
    then15.setHours(0,0,0,0);
    //use this to set how many days you want var then to be.
    then15.setDate(then15.getDate() + 15);

    // if there's no due date don't render anything. Also update the column names in the else statements below. If your column name has a space in its internal name use "_x0020_" in place of the space.
    if (MyDateVar <= today) {
        return "<div style='background-color:red;color:white'>" + ctx.CurrentItem.DueDate + "</div>";
    }
    else if (MyDateVar.getTime() >= then1.getTime() && MyDateVar.getTime() <= then14.getTime()) { 
        return "<div style='background-color:yellow;color:black'>" + ctx.CurrentItem.DueDate + "</div>";
    }
    else if (MyDateVar >= then15) {  
        return "<div style='background-color:green;color:white'>" + ctx.CurrentItem.DueDate + "</div>";
    }
}
</script>

 

 

 

 

 

 

5 Replies
best response confirmed by Joseph_Butler (Occasional Contributor)
Solution

@Joseph_Butler 

Not sure if this is exactly what you need. You should be able to modify where this code is located.

 

Before the first line of code in your, add the following.

 

if (!ctx.CurrentItem.DueDate || ctx.CurrentItem.Status.toLowerCase() === "completed") return;

 

This checks if the DueDate exists and then if the Status = Complete. If either condition is true, the function exits (return;)

 

If this works for you, please mark this response as the answer. Thanks!

The code provided removes the date from the Due Date field., so I updated it with the color:

if (!ctx.CurrentItem.DueDate || ctx.CurrentItem.Status.toLowerCase() === "completed")
return "<div style='background-color:white;color:black'>" + ctx.CurrentItem.DueDate + "</div>";
===? never seen that before.
This explains the difference:
https://howtodoinjava.com/javascript/javascript-equality-vs-identity-operators/
They both work in this case, but === removed the implicit typing step since we know both values are strings.
Sounds good. That's why I said in my answer that I wasn't exactly sure where you needed the code to go. The important part was the comparison piece and I was counting on you to adjust as needed.

Glad you got it working!