Calculated Time Left Columns in SharePoint with jQuery
Guest Author: Bil Simser
Site: Fear and Loathing
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!
Guest Author: Bil Simser
Bil Simser is an independent Solutions Architect with over 15 years in software development. He has helped build many large-scale mission critical systems along the way. In his role as a mentor, he guides clients on how to implement development standards and guidelines, evaluates and recommends new tools and technologies, and helps teams and projects progress into the .NET world. Bil also has a special interest in coaching clients on Agile and General Software Development Best Practices.
Bil has been involved with Microsoft’s .NET platform since the early betas and has a deep passion for good Architecture and Software Design. He specializes in SharePoint, .NET, Agile, TDD, and computer game programming. Bil also runs several successful open-source projects. He contributes to the software development community, taking the time to review and edit SharePoint and Agile publications, and speak at user groups, code camps, and conferences, including TechEd, DevConnections, PDC, and DevTeach.
Bil has been a Microsoft SharePoint MVP since 2004 and a member of the MSDN Canada Speakers Bureau. Bil currently lives and works in Alberta, Canada, with his wife, daughter, a Beowulf cluster of computers, every gaming console known to man, and a small menagerie of animals.
Great post, Bil!
Very nice post Bill; gotta love jQuery! :)
Bob Mixon
http://www.BobMixon.com
@Bil and Bob: interesting use case, but once again this has little to do with the power of jQuery. Date calculations and table traversing can easily be done with plain JavaScript (cf. countdown articles from last year on my blog).
The power of jQuery is more with Ajax, animations or complex manipulations (cf. articles from Paul Grenier or Jan Tielens).
So following on from Christophe’s comment would it be feasible to make this “Time Left” a dynamic field that automatically updates every second so you can see the time counting down?
Absolutely, you “just” need to display a dynamic timer instead of text in the field, using Flash or jQuery for example. This is one of the solutions Mark and I will be showing in our animations workshop.
I had a live demo with Flash on my old SharePoint site, but it seems to be down right now.
Update: the site is back, and the countdown live demo can be found here:
http://8002.freesharepoint2007.com/Lists/Tasks/AllItems.aspx
“Traffic Light”: hover over the bullet to read the remaining time.
“Countdown” uses the HTML calculated column plus a Flash component for the dynamic display.
Hi everyone,
I’ve tried this script and I got weird results, it’s showing me “Ended” all the time whatever the ‘End date’ column is showing ‘Timeleft’ calculated column ends up showing ‘Ended’, am I missing something? Or maybe is it because I have different time format like ‘2009.10.22 18:13′? Could this be problem? I’ve had some problems with Paul Grenier script – dealing with the [Today] problem, it gave me weird result ‘NAN min’ and it seemed like there were more people getting this but stil no workaround, I think here might be the same kind of a problem. Please help.
I can confirm my own theory, I changed locale of sharepoint site to show time format in 12 hours and script started showing correct time left. Is there a way to make script work with 24 hour time format as well? Thanx
@Bil: another thought, using this technique for online auctions may not be a good idea, as this client side script will naturally pick…the client’s date and time! So the calculated remaining time will depend on the visitor’s own clock.
This information is great!
Within the current jQuery, how would I capture another column’s values and use it to perform more if.. else tests?
Hi Bill
In your article, it states
Here’s the modified jQuery code:
There is no code.
Any chance of getting this ?
Iain
In your article you state “Here’s the modified jQuery code” but I am not seeing any hyperlink to the actual code. How can I access this code?
Thanks, Robyn.
You should be able to see the code now.
Best,
Natasha
Just curious. Since you are referencing an auction with the “Time Left” calculated field, can you recommend the best way to actually create an online auction using Sharepoint (i.e. survey, list, etc). Do you know of any good web parts that could be used to build it. I would prefer not to have to build it from the ground up, if possible.
Thanks.
Hi,
I did all the steps but could not get the Time left column updated and formatted. It’s only showing the value of End Date.
Same here, did you find a solution to this?
At the very top of the script, insert the call to jquery. For ex:
Hello everybody ! I have a question.
I use dd/mm/yyyy format in my list but it display wrong time left. Please help me to correct it.
Hi,
How would I add in a if statement, so that when a item has “Ended” it could use some css to hide the Edit icon for that item.
Also I have a work around for the UK date problem. Just create another calculated column =TEXT([End Date],”m/d/yyyy h:MM”) which converts the UK date format to US.
How can i just get a simple number of DAYS remaining (+/-) for the returned value. This is close by I dont need hour/minutes/seconds or the fancy formating. So much written about this with lots of solutions with bells and whistles, but no script that gives you a simple number of days left from a due date.
No script that gives you a simple number of days left? How about a quick Google search:
http://www.google.com/search?hl=en&q=sharepoint+countdown+%22days+left%22
Just a reminder of my previous comment:
- there is no “power of jQuery” here, and this can be achieved with plain JavaScript
- the auction example is dubious, as the method uses the local time, not a global reference