Countdowns – A second method
I have already published a method to display countdowns in SharePoint lists, and generally speaking compare a date to today’s date. My method uses the DVWP and Javascript.
Alexander Bautz, a reader from Norway, has come up with slightly different approach. He has mixed two of my posts, the countdown and the “HTML calculated column“, to create a script that achieves the same result. A significant advantage of his approach is that you don’t need SharePoint Designer.
Here is the script:
<script type="text/javascript">
<!-- Script created by Alexander Bautz - alexander@emskonsult.no -->
<!-- Inspired by Christophe@PathToSharePoint.com -->
<!-- This script searches for calculated fields that are "marked" vith "Due:" and -->
<!-- select them if they are less than today's date, or less than the "offset" as -->
<!-- specified in the configuration in the top of the script -->
<!-- Create a calculated field in the list with the following formula: -->
<!-- =IF(DueDate="","N/A","Due: "&MONTH(DueDate)&"/"&DAY(DueDate)&"/"&YEAR(DueDate)) -->
<!-- The data type returned from this formula is: Date and Time -->
<!-- The reason for returning Date and Time is that the date fields -->
<!-- displays in <nobr> tags and therefore easier to search for because -->
<!-- there are fewer <nobr> tags than <td> tags -->
<!-- ************************************************** -->
<!-- *************** Change these ******************** -->
<!-- *** yDaysOffset are the offset "Yellow Light" *** -->
<!-- *** rDaysOffset are the offset "Red Light" *** -->
<!-- ************************************************** -->
var yDaysOffset = 5;
var rDaysOffset = 0;
<!-- plus or minus on todaysDate: -->
<!-- minus marks the date x days after -->
<!-- plus marks the date x days before -->
<!-- ************************************************ -->
<!-- **** Do not change anything below this line **** -->
<!-- ************************************************ -->
// call script
findDatefields();
function findDatefields() {
// Find today's date
var todaysDate = new Date();
var arr = document.getElementsByTagName('nobr');
for (var i=0;i < arr.length; i++ ) {
// Check if it is "our field"
if(arr[i].innerHTML.indexOf("Due:") == 0) {
var sepDate = arr[i].innerHTML.substring(5).split("/",3);
var m = sepDate[0];
var d = sepDate[1];
var y = sepDate[2];
// build the datestring
var fieldDate = new Date(y,m-1,d,00,00,00);
var frendlyFieldDate = m + "/" + d + "/" + y;
// Round overdueDays with one decimal
var overDueByDays = (Math.round(((todaysDate.getTime() - fieldDate.getTime())/86400000)*10)/10);
// OffsetDate - Create the traffic lights
var date = new Date();
var yDay = date.getDate() + yDaysOffset;
var rDay = date.getDate() + rDaysOffset;
var Month = date.getMonth();
var Year = date.getFullYear();
var redOffsetDate = new Date(Year,Month,rDay,00,00,00);
var yellowOffsetDate = new Date(Year,Month,yDay,00,00,00);
if(yellowOffsetDate.getTime() < fieldDate.getTime() && redOffsetDate.getTime() < fieldDate.getTime()){
//alert("green");
arr[i].innerHTML = "<DIV title='Due " + frendlyFieldDate + "' style='font-weight:bold; font-size:24px; color:green'>•</DIV>";
}
else if(yellowOffsetDate.getTime() >= fieldDate.getTime() && redOffsetDate.getTime() < fieldDate.getTime()){
//alert("yellow");
arr[i].innerHTML = "<DIV title='Due " + frendlyFieldDate + "' style='font-weight:bold; font-size:24px; color:yellow'>•</DIV>";
}
else if(redOffsetDate.getTime() == fieldDate.getTime()){
//alert("red");
arr[i].innerHTML = "<DIV title='Due today!' style='font-weight:bold; font-size:24px; color:red'>•</DIV>";
}
else if(redOffsetDate.getTime() > fieldDate.getTime()){
//alert("red");
arr[i].innerHTML = "<DIV title='Overdue by " + overDueByDays + " day(s)" + "' style='font-weight:bold; font-size:24px; color:red'>•</DIV>";
}
}
}
}
<!-- For it to work in collapsed views -->
<!-- Tribute to Christophe@PathToSharePoint.com -->
function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {
var tbody=document.getElementById("tbod"+groupName+"_");
var wrapDiv=document.createElement("DIV");
wrapDiv.innerHTML="<TABLE><TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\">"+htmlToRender+"</TBODY></TABLE>";
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
findDatefields();
}
</script>
This specific script displays a traffic light, and shows date information on mouse over. You can of course adapt the rendering to fit your own needs.
The instructions are included in the above code:
- In your SharePoint list, create a calculated column and include Alexander’s formula
- Add a CEWP to your page, under your SharePoint list, where you’ll place the code.
Thanks for sharing Alexander!
I think this is one of the simplest solutions so far.
You need to set the properties of the calculated column to date format in the Date & tiime format and include that column on the web page for the traffic light to render.
Thanks for posting the script!
I must add that it was Christophe that added cross browser compatibility by replasing my innerText references with innerHTML and some other minor changes.
Regards
Alexander
Thank you Alex and Chris – exactly what I needed for my MOSS2007 lists. I had to change the calculated field formula “,” with “;” for SharePoint to accept it (maybe a regional setting).
Thank you Alex, I find the script very usefull, I just have one question, ¿Is there any way to filter the result of Due: column?
Let’s say I want only to see the yellow ones.
I found that what you see displayed is not the real value (it keps the due: date value)
Thanks
You’re correct, the countdowns proposed here are only rendering methods, the SharePoint list itself doesn’t know about today’s date.
For filtering, use the default SharePoint view filters. For example, to see the items due in less than 5 days set the two filters:
[Due Date] greater than Today
and
[Due Date] less than Today+5
Chris,
Thank for your quick answer.
Filtering helped a lot on my project, also the countdown from Alex is great.
I just have one final question:
Instead of using bullet (“&bull”) I want to use an image for the render, I change the style for “img scr=” with an http address but I can´t get it work. How can I do this?
Thanks again
No problem – just replase • with a tag like this:
Alexander
One more try:
Thanks Alex, works great
This looks like the solution I was looking for but I can’t get it to work….1 fundamental issue: Our date format in our country and in SharePoint is dd/mm/yyyy not mm/dd/yyyy.
I’ve tried messing with the code but think you have a funky mathematical equation on the date to work out the overdue days so it really is not working- any chance for some help?
Nothing funky here… everybody knows there are 86400000 milliseconds in one day
You’ll find more details on the calculation in my first post:
http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/
Actually Alexander’s initial script was for a dd.mm.yyyy format, and he changed it for publishing. Feel free to contact me or him by e-mail, and we can send you the original script.
Thanks for the update, will check that out once I resolved this. Is there a way, in a grouped view, to apply the HTML to the grouped column. When it display as the group label it displays the entire string div tags and all. Open the collapsed group and the collum displays perfect. I am hoping i do not have to create another column for the group labels.
thanks
Never thought about using the HTML column itself as group label… I’ll add this to my to-do list!
I am not sure if this is known, I added my countdown, days left using the Today trick, but this is not interactive. If I do not update the item it does not change. Did I miss something in the post? I saw the running countdown. that has the functionality iwant, but a little over kill. If I missed it just let me know.
Larry, what do you call the Today trick? are you talking about this:
http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/
The countdowns published on my blog use the Javascript Date()…and there is no trick!
Sorry again, I keep missing something small. Once I review it several times I finally get it. I really need to take a break. thanks for putting up with my posts. I got this script to work. I think this comes close to what I am looking for.
thanks again
This is just not working for me….I receive the error “The formula contains a syntax error or is not supported. ” I tried removing the “,” and replacing it with “;” then I tried adding “[ ]” around the field name. Same error. Any help would be appreciated. Thank you
The change mentioned by Ludo is a regional issue, so don’t change anything if you have the US settings. Other regional settings may require other adjustments.
cis, feel free to send me your formula by e-mail and I’ll take a look.
Is there a way to have Days remaining in one column and put Traffic lights in a different column?
Thanks
Can you help. I have added the formula above to a calculated colume and then I added a a CEWP to the bottom of the page and added the above code. Maybe I am putting to much of the code in the CEWP but when I look at my list it has a ton of code on the page and my list does not look any different? What am I doing wrong?
Terry: first make sure you added the script through the source editor of the CEWP, not the text editor.
Feel free to send me a screenshot: Christophe@PathToSharePoint.com
I finally did get it to work! Thanks! By chance is there a way to make the entire list item that color instead of just the one colume?
Yes. I plan to talk about this in the weeks to come.
Christophe:
Great work. This set up is exactly what we are after. I am new to SharePoint, and am having difficulty setting this up.
I have created a web parts page, and added a list with the duedate column, and another column with the code specified in line 9 above. I fear that the CEWP on the page is not functioning and not talking to the list at all… Is there a very simple code I could put in the CEWP to even see if it is communicating with the list? For example a code that would display “hello” in one of the list’s columns? Then I would know I’ve setup the page and CEWP is working, and proceed from there.
Thankyou in advance!
Martin: I have a post with simple and detailed examples here:
http://pathtosharepoint.wordpress.com/2008/12/09/color-coding-more-examples/
Also check out the troubleshooting page:
http://pathtosharepoint.wordpress.com/2008/11/01/troubleshooting-your-html-calculated-column/
Note that these two links are not directly related to countdowns.
Thank you Christophe, I will look into it straight away.
I have got text to html working now, but I have a question with the above script;
What column do the traffic lights appear in? Do I need to make a column for this?
Thankyou for your help once again
The role of the script is to replace text in the calculated column with HTML. The HTML will include whatever you decide, for example here the traffic lights.
So, to answer your question: the traffic lights will be displayed in the calculated column itself. In the above example, the calculated column is called “Status” and is set up with the formula:
=IF(DueDate=”",”N/A”,”Due: “&MONTH(DueDate)&”/”&DAY(DueDate)&”/”&YEAR(DueDate))
Thanks Christophe.
I must be having problems with the script, as it is not converting text to HTML. I have a column left over from testing which has html in it and it is displayed as text.
The column which should display the traffic light is simply displaying “Due: 2/19/2009″ etc.
Do I need to modify the script at all? The Text to HTML script was working perfectly before.
Sorry, I forgot to mention this issue and the workaround:
http://pathtosharepoint.wordpress.com/2008/12/08/countdown-plus-html-calculated-column/
And the adaptation for regional settings:
http://pathtosharepoint.wordpress.com/2008/12/22/countdown-with-regional-settings/
Martin, if you still have trouble after this, send me a screenshot:
Christophe@PathToSharePoint.com
I am trying this code, but the traffic lights aren’t appearing. I am getting due: date in the column instead. Is there something obvious I am doing wrong?
Well, maybe… Check the following:
- you put the code below the list on the page
- you put the code in the source editor of the CEWP
- your dates are in Month/Day/Year format. If not, check the code for regional settings:
http://pathtosharepoint.wordpress.com/2008/12/22/countdown-with-regional-settings/
Thanks for this. I have checked all these and it seems to be something to do with the date because the column (where the traffic lights shoud be) display date: m/d/year, where as the other dates are d/m/year. I have changed the order in the code, but it stills doesn’t appear. Any other ideas?
Rather than trying to change the code, it would be safer to use the other post I mentioned.
Do you have any other customizations in your page? There could be a conflict with another script.
It works! Thanks for the help!!
Just one other question… Can this work with Document Libraries or does it have to be a list?
Great solution, but I need highlight row in list instead of paste &bulls!!!
I tried to combine this article with
http://pathtosharepoint.wordpress.com/2009/02/26/highlight-rows-in-sharepoint-lists/
but it doesn’t work.
Do you know, how to highlight the row in list with CWP?
Christophe, Thanks for an excellent solution to a difficult problem. Have you looked into setting the traffic lights based on %complete in the task list? My javascript is not very solid, so my attempt to mod the code produced errors. I was trying for a formula such as:
yellow_leeway = .1; \\ variable to say if behind schedule but not by more than 10% then yellow light
if ((today-start_date)/(due_date-start_date) < yellow_pct) {green_light code;}
else if ((today-start_date)/(due_date-start_date) – yellow_leeway < yellow_pct) {yellow_light code;}
else {red_light_code;}
Hello, I’m new to your site and now couldn’t do without it…This is a great post, and I’ve gotten everything to work perfectly. But I do have one requirement, how do you get the traffic light to stop showing overdue? If the “task” has been completed and is no longer overdue how do you get the mouse over text to show completed and the color to change to something else (black for example). Is this possible?
Thanks!
Thanks Alex/Christophe – great article. I have modified the script slightly so that it flags an entry as overdue if a last edit date is over 7 days ago. I’m now trying to get this to work when the view is grouped – I hope this is where the second function above for ‘collapsed’ views comes in? Where is this called from? I figured it would be from the if statements but then got confused by the call to the parent finddateFields function at the end. Also, how do you reference the tbody id for the groupName parameter passed to the function?
Thanks!
Dan: the ExpGroupRenderData is a default function that SharePoint calls when expandind groups. The above script modifies it slightly to include the countdown rendering (finddateFields function).
Aaaah, thanks Christophe – all working now
Jen: the easiest way would be to modify the formula of the calculated column, and add a condition: if task completed, then “N/A”.
Is there a way to use this method for a form library rather then a list ?
I have a task list based on InfoPath forms and there is a due date column in this library. I tried adding the due date status column and added the script to the page but it’s not working. (it’s not even doing the calculation.. just gives me a “Due: x/x/2009″.
Thanks,
Netta
Hi Christophe,
I am trying to use your calculated column method along with Alex’s countdown method as you described on your – excellent – website http://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
After creating the ‘due’ calculated column and copy/ pasting the script in the source editor of the CEWP (CEWP containing your script for the calculated column method and Alex’s script for the ‘due date’ are both placed below my list), it seemed that everything worked:
- the calculated column was displaying correctly – both before and after copy/pasting the ‘due date’ script (see snapshots).
However, the calculation itself doesn’t seem to work: a due date of 04/30/09 was displaying as overdue by 120.6 days (current today’s date being 04/29/09)
The script gave the following results:
Due Date Overdue by:’
11/30/2008 455.6
4/30/2009 120.6
5/5/2009 145.6
5/15/2009 135.6
5/31/2009 119.6
6/15/2009 135.6
6/30/2009 120.6
9/9/2009 141.6
Any idea what could cause this issue?
Your help would be greatly appreciated at http://pathtosharepoint.wordpress.com/2008/11/24/countdowns-a-second-method/
Thanks,
Greg
PS: I am using the US date format and didn t modify the script posted
PS2: I am trying to use this on a ‘regular’ list, NOT a task list
Greg, which “script for the calculated column” are you referring to? Maybe this is what you’re looking for:
http://pathtosharepoint.wordpress.com/2008/12/08/countdown-plus-html-calculated-column/
Got it to work!!!
Thank you so much for all the work and your awesome blog!
Hi Christophe,
At first, I used 2 separate webparts underneath the list view:
– one with your script for the calculated column
– one with the script for the countdown
I noticed you had a script for countdowm + calculated column, so I replace the 2 webparts with a single one.
In both cases, the icons are displayed and gives the appearance everything is right. However, the overdue calculation itself doesn t work.
Awesome work, thanks for another great post! This “real aging” of SharePoint record dates has been a pain in my side for a long time. I am feel I am very close to making this work the way i need it to…i’ve got the bullets down but i’m going for something more like the count down (in days). I don’t have access to SP Designer in my environment, and I am handicapped by my poor javascript understanding. Instead of “&bull” I would like to return either today’s actual date in mm/dd/yyyy format or if that’s not possible return the number of days until due as in the post (but done in script since SP Designer is not available).
Thanks again – really hope someone has a solution!
@ Vincent
Had the same issue for a long long time.
When calculating between 2 ‘fixed’ dates, a good method is to create a calculated to create the html text string and then add Christophe script to ‘read’ the html.
When you need to use ‘Today’s’ date, well, the calculation needs to happen in the Javascript code (and not in Sharepoint).
Alexander method consists of 2 steps:
– a calculated column to produce a text string specific enough for the code to recognize it
[=IF([Furniture Order]=”",”N/A”,”Due: “&MONTH([Furniture Order])&”/”&DAY([Furniture Order])&”/”&YEAR([Furniture Order]))]
@ Vincent
– a script taking the info from that specific text string, doing all the calculations and creating the proper conditional html code.
So you need to do your customisation in the javascript code.
I modified Alex code to produce KPIs with values using Webdings/ Wingdings fonts. Below is the modified portion of the code for one of the conditions.
//alert(“yellow”);
arr[i].innerHTML = “u [+"+ overDueByDays +"d] “;
@ Vincent (Part 3 of 3)
It needed some adjustments to get the html right but this allows you to have a KPI with a value in the same column….
Hope this helps.
PS: I used similar html coding for 2 fixed dates and the text to HTML code from Christophe. In that case, you do your ‘customisation in the Sharepoint calculated column:
=IF(OR(ISBLANK([User Need Date]),ISBLANK([Troop Ready])),”",”0,” ‘font-size: 11px; font-family: Tahoma; color:#4CC417′> (+”&ROUND([User Need Date]-[Troop Ready],0)&”d)u”,” ‘font-size: 13px; font-family: Wingdings 3; color:#FF0000′>t (“&ROUND([User Need Date]-[Troop Ready],0)&”d)”)&”")
//alert(“yellow”);
arr[i].innerHTML = “u [+"+ overDueByDays +"d] “;
I apologize for the multiple posts…
Greg
Hi.
First of all I would like to say this is my first comment ever and that I really appreciate all the help I’ve been getting from all these posts.
Now here is my problem: I am a business student and have absolutely no idea how to edit the scripts I find here and I’m having an awful amount of trouble to do these ‘Due Date Traffic Lights’. My problem begins with the fact that I can’t even modify the ‘calculated column/field’ script to work with due dates.
If anyone could be so kind of maybe posting a really easy ‘how-to’ I would really appreciate. Or even a ‘ready-to-use’ script to make some traffic lights appear next to my due dates.
Thank you very much.
Great Post!
I am not very good at modifying code and could use some assistance. My organization requires 4 different colors based on number of days from due date (>60 = blue, 31 to 60 = green, 15 to 30 = yellow, and <= 14 = red). Thank you in advance!
Todd
This post is absolutely AMAZING!!!
It works brilliantly! Thanks Christophe and Alex!
I’m new to this site – I’m still unable to get this to work. I’ve read everything and tried every suggestion – and like someone else above, all I see in my list is Due: 1/22/2010 and not the traffic light. I have no idea what I’m doing wrong. I have my list with my calculated column as indicated. the CEWP with the code above has been inserted below the list. and yet nothing is displaying. Any help would be appreciated.
BTW – I’m implemented at least 8 of the other code found in these blogs as submitted by Christophe and they are AMAZING. I’m now the office hero
Is there a reason it works on one view but not other views? Do I need to add the CEWP to every view?
Scott: you need the CEWP on every page where you want to render the text as HTML.
Great article! works like a charm, thank you Christophe and Alex! One issue is that exporting list to spreadsheet does not work anymore; giving the error: “Cannot get the list schema property from the sharepoint list” This is an issue with date fields but the workaround given does not work for my calculated (traffic) column. Has anyone encountered this? Any ideas? I have Excel 2003
Thanks again!
If you read this and cannot get the TRAFFIC LIGHTS to show in the Status Column, make sure you create the Status column as Calculated (calculation based on other columns), BUT “The data type returned from this formula is: ” Date and Time and is in a Date/Time format.
Hope this tip helps.
How can I get this script to work in SharePoint 2010?
Hi,
I am trying to do this on a Gantt chart view and it doesn’t allow me to post my CEWP below my columns. I added the CEWP on top, and nothing happened
That’s strange. Have you tried to change the Web Part index, to 100 for example, to move it to the bottom of the zone?
Works as advertised, thanks for this solution. I’m showing the traffic lights in my column with the number of days on mouseover. What changes (I’m not much of a programmer) do I need to make to display the number of days in the column rather than the traffic lights?
Dave, I recently published another countdown method, maybe it would work better for your needs?
Simply brilliant and exactly what I was looking for.
Afternoon
I have this kind of working – I have a DueDate column which has the date of expiry in it. I have another column called Countdan which is a calculated column with the formula at the top of the script – and that is working, I get Due: xx/xx/xxxx in the coluimn.
What I cannot seem to figure out is how to or what I am supposed to add into the Status column.
Any help would be appreciated.
Iain, the Status column is the calculated column that outputs Due: xx/xx/xxxx. The script then transforms it into an indicator.
Hi Chris,
It’s a great article. I followed the instruction, adding the code to my SharePoint 2010 site. It works great!
But after I added Grouping By function to the list view, the color coding no longer works.
Any idea?
Please help! Thanks in advance,
It’s quite possible that the script is broken in SP 2010 – I haven’t tested it. Note that there are more recent countdown articles in my blog that could work for you.
Also, try to use expanded, not collapsed, as default setting for the grouping.
Hello Christophe,
First of all thanks for lot for coming up with such a wonderful and helpful sites for novice users like me !!
This article has really helped me and I am able to see the red/green/yellow button on my sharepoint, which I had to prepare on a short notice. This site has really helped me, however I have some customization to be made and need your urgent help.
I have made DueDate to be the date when last modification on the item was made, therefore its actually ‘Modified Date’.
Now what I am doing is checking how long since last modified date is today’s date and also checking another column called status.
Based on that I will show red/amber/green button and also show ageing of the item.
So,
If [status] column is ‘XYZ’ AND difference between due date(i.e. last modfied date) and today’s date is
0-1 days : Green
1-2 days: Amber
more than 2 days: Red
This will basically tell me how long has the item being in a particular status. This will tell me ageing of the item.
I am assuming that this customization would be a minor change in your script (as in the blog) and I would really appreciate if you can help me !!
Eagerly waiting your help.
P.S: I have sent you private email as well. I am posting it here because its really urgent.
Thanks
Sid
Comparing dates with today is actually a major change to the script, and not very easy in SharePoint. Search my blog for “countdown” to find some solutions.
can any one help me how to customise the look & Feel of the customlist used in webpart in sharepoint 2010 by the step by steps
i got a problem while creating page. After i creating a page by giving name it showing chekot & Editable for the publishing site how to disable that status msg