Using SharePoint 2016. Trying to write JavaScript to change row color based on today's date and "SuspenseDate" in list. Help?

Question

I would deeply appreciate any help. I have been staring at this code for days. I am trying to add a Script Editor web part above a list, and make the rows in the list change color according to how close today's date is to a suspense date (SuspenseDate column in the list.) I want to have a different column for items in these three circumstances: past due, 1 day before due, 2 days before due

This code does nothing, and I can't figure out why, despite hours of troubleshooting. My SuspenseDate column shows dates in standard format, with no time. (ex. 08/08/2023)

I have been banging my head against the wall on this for days. Please let me know if you have any ideas.

<script>
(function () {
    'use strict';

    var overrideCtx = {};
    overrideCtx.Templates = {};
    //OnPostRender call postRenderHandler function.
    overrideCtx.OnPostRender = postRenderHandler;

    // Register the template overrides.
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);   

})();
function postRenderHandler(ctx) {

            // get today's date
            var today = new Date();
            // zero out the time portion so we will only compare days
            today.setHours(0,0,0,0);



            var rows = ctx.ListData.Row;
            for (var i = 0; i < rows.length; i++) {

                // get the date set in your date YourDateField
                var suspenseDate = new Date(rows[i]["SuspenseDate"]).toISOString();

                var rowId = GenerateIIDForListItem(ctx, rows[i]);
                var row = document.getElementById(rowId); // so we know which row to change

                // get date 24 hours before suspense date
                var suspense24 = new Date(suspenseDate);
                suspense24.setDate(suspense24.getDate() - 1);
    
                // get date 48 hours before suspense date
                var suspense48 = new Date(suspenseDate);
                suspense48.setDate(suspense48.getDate() - 2);
           


                if (today > suspense24) {
                    row.style.backgroundColor = '#FF5733';
                } 
                if (today == suspense24) {
                    row.style.backgroundColor = '#FFC300';
                } 
                if (today == suspense48) {
                    row.style.backgroundColor = '#DAF7A6';
                } 
            }
}
</script>

Answer 1

There's a couple things going on here. One is that, whether or not you see it in the SharePoint UI, even if you have a date column set to be "date only", behind the scenes there is still a time component to a Date object.

Second, you can't compare Date objects using ==. If you want to do that you would need to call getTime() and compare the resulting numbers. But you don't need to, you can use >, <, >= and <= directly with Date objects.

I would try restructuring the code inside the for loop like this:

// get the date set in your date YourDateField
// but DON'T turn it into an ISO string, you still
// want it as a Date object
var suspenseDate = new Date(rows[i]["SuspenseDate"]);

// but you still probably want to zero out
// the time portion of the date so that it's
// comparing relatively equally with the "today"
// date that has time zeroed out
suspenseDate.setHours(0, 0, 0, 0);

var rowId = GenerateIIDForListItem(ctx, rows[i]);
var row = document.getElementById(rowId); // so we know which row to change

// get date 24 hours before suspense date
var suspense24 = new Date(suspenseDate);
suspense24.setDate(suspense24.getDate() - 1);

// you don't need to zero out the hours here because
// suspense24 was created by cloning suspenseDate
// which already has time zeroed out

// get date 48 hours before suspense date
var suspense48 = new Date(suspenseDate);
suspense48.setDate(suspense48.getDate() - 2);

// it's overdue if it is greater than suspenseDate,
// (or is exactly on the suspenseDate?)
// not greater than suspense24, because suspense24
// is still one day _before_ the suspenseDate

if (today >= suspenseDate) {
    row.style.backgroundColor = '#FF5733';
}
// it's within one day if it's equal (or greater) than suspense24
// but _before_ the suspense date
if (today >= suspense24 && today < suspenseDate) {
    row.style.backgroundColor = '#FFC300';
}
// it's within two days if it's equal (or greater) than suspense48
// but _before_ suspense24
if (today >= suspense48 && today < suspense24) {
    row.style.backgroundColor = '#DAF7A6';
}

Answer 2

To all who will read this in the future. The solution ended up being following Ganesh's advice above to find the real column name, plus taking into account what Dylan said.

Below is the working code:

<script>
(function () {
    'use strict';

    var overrideCtx = {};
    overrideCtx.Templates = {};
    //OnPostRender call postRenderHandler function.
    overrideCtx.OnPostRender = postRenderHandler;

    // Register the template overrides.

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);   

})();
function postRenderHandler(ctx) {

            // get today's date
            var today = new Date();
            // zero out the time portion so we will only compare days
            today.setHours(0,0,0,0);



            var rows = ctx.ListData.Row;
            for (var i = 0; i < rows.length; i++) {

                var suspense = new Date(rows[i]["Suspense"]);
                suspense.setHours(0,0,0,0);

                // get date 24 hours before suspense date
                var date24 = new Date(suspense);
                date24.setDate(date24.getDate() - 1);


                // get date 48 hours before suspense date
                var date48 = new Date(suspense);
                date48.setDate(date48.getDate() - 2);
                //us48 = date48.toLocaleDateString();

                var rowId = GenerateIIDForListItem(ctx, rows[i]);
                var row = document.getElementById(rowId); // so we know
which row to change

                if (today >= date24 && today <= suspense) {
                    row.style.backgroundColor = '#FFC300'; //lightorange,
due today/tomorrow

                }
                else if (today >= date48 && today <= date24) {
                    row.style.backgroundColor = '#DAF7A6'; //yellowgreen in
2 days
                }
                else if (today > suspense) {
                                row.style.backgroundColor = '#FF5733';
//orange, past due
                        }
            }
}
</script>

Answer 3

Make sure you are using correct internal name of your suspense date column in the code at line:

var suspenseDate = new Date(rows[i]["SuspenseDate"]).toISOString();

You can get the exact internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

You can use the "classic experience" method (via list settings) given in above article link to find the internal name of list column or use SharePoint REST API (directly from browser tab). If you are not using correct internal name of column then your code will work as expected.

Also, refer below answers for using Client Side Rendering (CSR) / JSLink for customizing display of date columns in the SharePoint lists:

1. How to color code a SharePoint list date field based on comparison with todays date?
2. Conditional Formatting with date comparison
3. How to Highlight a Row on Active Status