Calculated Time Left columns in SharePoint with jQuery

A current project I’m working on in SharePoint is an online auction. I’ll post more info about this and maybe some code and web parts later but for now I wanted to share a simple enhancement we did with a little jQuery to display the time left for each item.

Just like eBay, I wanted to display the time remaining on auction items. I figured this would be a calculated field (based on a date the user chose for when the auction for that item ended) but having to calculate date differences based on the current date doesn’t work in SharePoint (the elusive [Today] problem). I thought jQuery would help and it did. Here’s how.

First you need a couple of fields in your list. Auction items are stored in a list with some details (title, description, bid information, etc.).

Along with the regular fields there are a few other ones at the end that are used for housekeeping on the item (and not displayed to users). In particular there’s an [End Date] field which is a simple Date/Time field for when this item should end and another one called [Time Left].

[Time Left] is a calculate field shown as a Date/Time value. The actual calculated value is irrelevant as we’ll be replacing it with our JavaScript, so we just make it equal to the [End Date] field.

The calculated column serves as a dual purpose because we’re actually going to read this in our List View then replace it with the number of days/hours/minutes/seconds remaining on the auction item. Since it’s a calculated field, the user doesn’t edit it either.

Over at End User SharePoint in the jQuery for Everyone series Paul Grenier, the undisputed King of jQuery in SharePoint, had a great article about dealing with the [Today] problem. His sample finds a column in a list view and displays when an item was last updated. It’s exactly what I needed, except I needed to look forward in time to determine the time remaining rather than backwards. Simple enough to take his example and reverse the dates. Here’s the modified jQuery code:

<script type="text/javascript">
    $(document).ready(function(){
        var str = "Time Left"; //change this based on col header 
        var a=0;
        var headers = $("table.ms-listviewtable:first> tbody> tr:first th").get();
        $.each(headers, function(i,e){
            x = $(e).contents().find("a[title*='"+str+"']").length;
            a = x > 0 && i > a ? i : a;
        });
        var today = new Date();
        today = Date.parse(today)/1000;
        var dArray = $("table.ms-listviewtable:first> tbody> tr:gt(0)").find(">td:eq("+a+")").get()
        $.each(dArray, function(i,e){
            var d1 = Date.parse($(e).text())/1000;
            var time = '<span style="color:#ff0000">Ended</span>';
            if(d1-today > 0) { 
                // calculate days, hours, minutes and seconds
                var dd = (d1-today)/86400; 
                var dh = (dd-Math.floor(dd))*24;
                var dm = (dh-Math.floor(dh))*60;
                var ds = (dm-Math.floor(dm))*60;
                // build display string
                time = ((Math.floor(dd) > 0 ? Math.floor(dd) +"d " : "")+
                        (Math.floor(dh) > 0 ? Math.floor(dh)+"h " : "")+
                        (Math.floor(dm) > 0 ? Math.floor(dm)+"m " : "")+
                        (Math.floor(ds) > 0 ? Math.floor(ds)+"s" : ""));
                // highlight active auctions
                var isEndingSoon = (((Math.floor(dd) + Math.floor(dh)) <= 0) && (Math.floor(dm) < 15)) ;
                if(isEndingSoon) { time = '<span style="color:#ffcc00">' + time + '</span>'; }
                else { time = '<span style="color:#005a04">' + time + '</span>'; }
            }
            // write out text value as html
            $(e).text('<span style="font-weight:bold">'+time+'</span>');
            $(e).html($(e).text());
        });
    });
</script>

Remember, the [Time Left] column is a calculated value that’s simply displaying the value from the [End Date] column the user enters. Why not just use the [End Date] field? Simple. It makes more sense when editing an item to set the End Date but then display the time remaining. Imagine if you were editing this and saw a field called “Time Left” as a Date/Time value. This way, the user sets the End Date for the auction but never sets the calculated field. We also needed the target date to be displayed in the list view so we could do our calculation so here it is.

Finally I did a little formatting on the time remaining. All items are set in a bold font and coloured green. Items that are ending in less than 15 minutes are coloured orange (yellow doesn’t show up very well against a white background) and items that have ended already are displayed in red.

Once this little jQuery script was written it was a simple matter of going to the list view page, editing it, and adding a Content Editor Web Part with our script in it. You could also use this on a Web Part Page as long as you have the [Time Left] field visible in the view. Here’s the list of items with the Time Left field and coloured highlighting:

Pretty cool and again, thanks to the power of jQuery (and Paul) no assemblies or server deployments needed!