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.).

image

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].

image

[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.

image

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:

clip_image002

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

9 Comments

  • Thanks for sharing this one. Was looking for a solution for calculating remaining time within MOSS.

  • Hi,
    I was wondering whether you have done a guid on how to create the auction site?

  • Hi,

    I'm also wondering if there's a guide. I've created the list, columns, and added the code to the list page, but it's not working for me.

  • Thank you for the script! Got it working in MOSS 2007 but haven't been able to get it working SP 2010 yet. As is, the script doesn't reformat the Time Left field AND it hides the List Tools menu at the top of the page. Will repost if/when I find a solution. Any pointers would be appreciated too.
    PS: the [Today] issue doesn't seem to be resolved in 2010 either...


  • I am new to SharePoint. I was just tasked with creating an online auction for my company. I have no idea where to even begin. Can anyone please outline how this needs to be accomplished? Any code would be greatly appreciated.

  • Do you have the codes for the whole bidding site?

  • Peter, I don't have the code for the entire posted right now but will be publishing a blog this week with the entire auction site code.

  • I have a page with multiple list view web parts. This only applies to the first list view web part it finds on the page. How can I make it apply to all of them? (It also only works if my Time Left column is the first column in the view).

  • Hi Bil, can you advice where we can read your blog which have the entire auction side code?

Comments have been disabled for this content.