15.09.2009: Complete rewrite of the script to tidy up the code, improve functionality, and to add jQuery-logic to make it run smoother.
This script is used to Compare a “DueDate” in a list view with “Today” and generate “traffic lights” to flag status. It also adds a mouseover tooltip that shows the list element’s “day, hour and minute – status”.
The end result looks like this:
You can filter on the “Status-column”:
As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a subsite named “test” with a subsite named “English” with a document library named “Javascript”):
Add a CEWP to your list view – below the list form, and add this code:
<script type="text/javascript" src="/test/English/Javascript/jquery-1.3.2.min.js"></script> <script type="text/javascript" src="/test/English/Javascript/trafficLightsOnDueDate.js"></script> <script type="text/javascript"> // Offset yOffsetMinutes = 2880; // When to show the yellow "flag" rOffsetMinutes = 1440; // when to show the red "flag" // Order of apperance of the dateFields in your Formula (1 = first position, 2 = second position and 3 = third position) OrderDay = 2; OrderMonth = 1; OrderYear = 3; // Settings defaultTimeIfDateOnly = '12:00'; // If used with "Date Only" - what time should be used as "DueTime" - Military Time splitChar = '/'; // The separator between the day month year of your calculated dateField: 01[splitChar]01[splitChar]2009 separatorDateTime = ' '; // The separator in the calculated column between date and time: 01/01/2009[separatorDateTime]08:00 LeadInTextBeforeDate = 'Due:'; // The leadin text of your calculated dateField - used to locate the correct table cells // Tooltip on "traffic lights" before days/hours/minutes TooltipDueIn = 'Due in: '; TooltipOverdue = 'Overdue: '; // Tooltip on "traffic lights" after days/hours/minutes TooltipDays = ['day','s']; // Array - first value for "singular", second for "plural" TooltipHours = ['hour','s']; // Array - first value for "singular", second for "plural" TooltipMinutes = ['minute','s']; // Array - first value for "singular", second for "plural" // Call script trafficLightsOnDueDate(); </script>
Like this:
The script “trafficLightsOnDueDate.js” has this soursecode:
/* Compare DueDate with Today in list view and generate "traffic lights" from DueDate
* ----------------------------------------------------
* Author: Alexander Bautz
* alexander.bautz@gmail.com
* Version: 2.0
* LastMod: 15.09.2009
* ----------------------------------------------------
* Must include reference to jQuery
* ----------------------------------------------------
*
* All variables are defined in a CEWP below the list view of your list. See separate codeblock for code.
*
* ----------------------------------------------------
*/
function trafficLightsOnDueDate() {
// Find today's date
var now = new Date();
$("td.ms-vb2:contains('" + LeadInTextBeforeDate + "')").each(function(){
var sepDate = '';
var sepHourMinutes = '';
if($(this).text().substring(LeadInTextBeforeDate.length + 1).length>10){
sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1,$(this).text().lastIndexOf(separatorDateTime)).split(splitChar);
sepHourMinutes = $(this).text().substring($(this).text().lastIndexOf(separatorDateTime)+separatorDateTime.length).split(':');
IncludeHoursAndMinutes = true;
}else{
sepDate = $(this).text().substring(LeadInTextBeforeDate.length + 1).split(splitChar);
IncludeHoursAndMinutes = false;
}
var m = sepDate[OrderMonth - 1];
var d = sepDate[OrderDay - 1];
var y = sepDate[OrderYear - 1];
if(IncludeHoursAndMinutes){
h = sepHourMinutes[0];
min = sepHourMinutes[1];
}else{
h = defaultTimeIfDateOnly.split(':')[0]
min = defaultTimeIfDateOnly.split(':')[1]
}
// Build the datestring from fieldvalue
var fieldDate = new Date(y,m-1,d,h,min,00);
// Build friendly string for due/overdue mouseover tooltip
var DueByDays = msToDHM(Math.round((fieldDate.getTime() - now.getTime())));
var overDueByDays = msToDHM(Math.round((now.getTime() - fieldDate.getTime())));
// OffsetDate - Create the traffic lights
var yellowOffsetDate = now.getTime() + (yOffsetMinutes * 60000);
var redOffsetDate = now.getTime() + (rOffsetMinutes * 60000);
if(yellowOffsetDate < fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
// green
$(this).html("<div style='font-weight:bold; font-size:16px; color:green'>•</div>");
$(this).parent().attr('title',TooltipDueIn + DueByDays);
}
else if(yellowOffsetDate >= fieldDate.getTime() && redOffsetDate < fieldDate.getTime()){
// yellow
$(this).html("<div style='font-weight:bold; font-size:16px; color:yellow'>•</div>");
$(this).parent().attr('title',TooltipDueIn + DueByDays);
}
else if(redOffsetDate > fieldDate.getTime() && now.getTime() < fieldDate.getTime()){
// red due in...
$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
$(this).parent().attr('title',TooltipDueIn + DueByDays);
}
else if(redOffsetDate > fieldDate.getTime() && now.getTime() > fieldDate.getTime()){
// red overdue by...
$(this).html("<div style='font-weight:bold; font-size:16px; color:red'>•</div>");
// Red text in all row (but the title with link)
$(this).parent().attr('title',TooltipOverdue + overDueByDays).find('td').addClass('ms-errorcolor ms-bold');
}
});
}
function msToDHM(ms){
var countDays = Math.floor(ms / 86400000); // 86400000 in one day
var countHours = Math.floor((ms % 86400000) / 3600000); // Find "leftovers" after all the full days have been taken away / 3600000 ms in one hour
var countMinutes = Math.floor(((ms % 86400000) % 3600000) / 60000); // Find "leftovers" after all hours have been taken away / 60000 ms in one minute
var str = '';
if(countDays!=0){
if(countDays>1){
str += countDays + " " + TooltipDays[0] + TooltipDays[1] + " ";
}else{
str += countDays + " " + TooltipDays[0] + " ";
}
}
if(countHours!=0){
if(countHours>1){
str += countHours + " " + TooltipHours[0] + TooltipHours[1] + " ";
}else{
str += countHours + " " + TooltipHours[0] + " ";
}
}
if(countMinutes!=0){
if(countMinutes>1){
str += countMinutes + " " + TooltipMinutes[0] + TooltipMinutes[1];
}else{
str += countMinutes + " " + TooltipMinutes[0];
}
}
if(countDays + countHours + countMinutes == 0)str += " 0 " + TooltipMinutes[0] + TooltipMinutes[1]
return str;
}
// Attaches a call to the function "trafficLightsOnDueDate" to the "expand grouped elements function" for it to function in grouped listview's
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);
trafficLightsOnDueDate();
}
Add these columns to your list:
- a column of type “Date and Time”, format “Date Only” or “Date & Time” named DueDate
- a column of type “Yes/No (check box)” named Completed
- a column of type “Calculated (calculation based on other columns)” named Status
The DueDate-column does not have to be in the list view, but the calculated column must be.
The column named Status has this formula for “DateOnly”:
=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy"),"Completed"))
And this formula for “Date & Time”:
=IF(DueDate="","N/A",IF(Completed<>TRUE,"Due: "&TEXT(DueDate,"mm/dd/yyyy HH:mm"),"Completed"))
This has formula for “Date & Time”:
Thats it!
Alexander
Hi, This is exactly what I am looking for to complete a minor project I am undertaking currently. I have a few questions as I am an administrator doing the job of a developer at the moment so as you can predict it will be boring stuff like how and where does this script go etc.
Would you mind answering questions of this nature?
This may work and if so then thank you, but, Lotus Notes had this functionality Out-Of-The-Box, 15 yrs ago. I cannot comprehende how Microsoft can claim they have an Enterprise Solution that does not provide this feature with nothing more than a few clicks for each view. Dispicable. Nothing new for Microsoft though – a company that should stick to games instead of destroying the world with dysfunctional technologies for business.
Not at all – feel free to ask. I have been to busy to update this blog, but i have made a new version of this script. I will post it later today.
I have updated the script.
Thank you – much appreciated. From what I have read here my understanding is that when creating the date column I am to use the formula as you have provided above – then in a content editor web part below the list I add the Jscript … is it then a case of hey presto? Do I need to call the column DueDate?
No – only refer the “original” date-column in the formula in the calculated column you create. It should work with only the steps you describe, but remember to use a datatype of “Date and Time” in your calculated column.
The “original” date-column you use as source for your calculated column does not have to be in the view at all.
Great stuff thank you very much! This solution is very useful for me as my company has gone down the route of buying the standard edition of SharePoint and I can not use Data Views which are part of the enterprise edition! Madness if you ask me – just another victim of the Credit Crunch haha!
Unfortunately I am getting the error below when creating the duedate column;
The formula contains a syntax error or is not supported
Is it some thing nooby that I am doing here? I simply copied and pasted your line;
=IF(DueDate=””,”N/A”,”Due: “&MONTH(DueDate)&”/”&DAY(DueDate)&”/”&YEAR(DueDate)) and then changed to match my column name…
Do you use english language SharePoint? -if not maybe the formula syntax is incorrect in your language?
yep it is for a site in England … Though it might be down to the date format in the formula so that day comes first then month the n year but that didn’t do anything either.
The status column how is that created? I`m guessing you simply create a custom column with status as the title??
I have read a round and made some ammendments to the formula according to what I have read – however there is still a bad syntax somewhere in there.
=IF([Expiry Date]=””;”N/A”;”Due:”&MONTH[Expiry Date]&”/”&DAY[Expiry Date]&”/”&YEAR[Expiry Date])
Thank you for your support it is much appreciated.
You missed the brackets – try this formula:
=IF([Expiry Date]=””,”N/A”,”Due: “&MONTH([Expiry Date])&”/”&DAY([Expiry Date])&”/”&YEAR([Expiry Date]))
You have to manually rewrite the quotes – it is the wrong format when you cut and paste from this text.
Alexander, how did you make so that the caption on the Status Filter Drop-Down was “Due: 11/23/2008”, as opposed to the HTML code that it actually has? TIA.
I got it, thanks!
Sorry for not replying – have been very busy at work this week… glad you figured it out!
I’d like to combine 2 of these ideas but cannot find the internal column name for a workflowstatus column.
ie I want to traffic light as you’ve got but add a second condition to the lights checking the status of the associated ‘Approval’ workflow. I can’t seem to get the type and fieldname of this column to compare.
Can you provide an example?
I missed this comment…
Sorry, but it’s not possible to use “workflow columns” in calculated column’s formula.
Hi,
i am using the Room and Equipment reservation template, where i have start date to be selected.
what i need is how can i stop users from selected date less than todays date and time.
thanks for any help.
Hi, great work!
I made it work easy.
Now I need to do the same but with minutes instead of days. You see, we have a ticket system that handle requests that must be taken from queue in minutes, the minimun amout of time being 15 min.
I tried to modify your code with no success. Can anyone give me a hand?
Thanks
Hi Rodrigo,
I believe you can use the script to fill your need now that it’s updated.
Drop me a line if you can use it – or if something is not as expected.
Regards
Alexander
How would you add a column that displays the day remaining from duedate to todays date?
Hi,
To display the days in the column you must edit the script like this:
// Example for Green (line 55) $(this).html("<div style='font-weight:bold; color:green'>" + DueByDays + "</div>"); // Example for Red (line 65) $(this).html("<div style='font-weight:bold; color:red'>" + overDueByDays + "</div>");The default format is “days hours minutes”, but you can edit the function msToDHM() to return days only.
Alexander
Hi, Alexander,
Firstly, thank you for the great work – the soution is useful.
Unfortunately the script does not work if your list view contains grouping by a column of Lookup type or by Assign To. In this case the data in your Status column is shown straight from Excel formula, not the coloured dots as expected. Could you please check?
Hi,
I did a quick test and it does work for me when grouped by a lookup column and on “Modified By”. Are you sure you have set it up correctly? – can you test by removing the grouping?
Alexander
Try with this, it works for me…
function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {
var tbody=document.getElementById(“tbod”+groupName+”_”);
var wrapDiv=document.createElement(“DIV”);
wrapDiv.innerHTML=””+htmlToRender+””;
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
trafficLightsOnDueDate();
}
Thanks for the help Alex – Any way to utilize this in a Gantt view highlighting the tasks bar?
Hi,
Sorry, but i have not tested it with Gantt view’s. I know Christophe over at Path to SharePoint has loads of tricks for Gantt view’s – check out his site and see if you find what you are looking for.
Regards
Alexander
I can’t get this to work. I’m thinking maybe I don’t understand the part “Add a CEWP to your list view – below the list form.” I have a list and I opened its “RssView.aspx” file in the SharePoint Designer, and added a Content Editor Web part in one of the available PlaceHolderUtilityContent areas at the bottom of the page. Is this OK? (I’m new to SharePoint, so I don’t really know what I’m doing).
I don’t get the nice traffic lights and colors, just the “Due: 11/23/2010″ type text.
Ok. I tried again by adding to the AllItems.aspx, and I found your hints about adding the “?toolpaneview=2″ to the URL to get the editor you are using. I inserted the CEWP beneath my List View Web Part, and added the code using the Text Entry dialog. So it seemed like I was on the right track. But it didn’t work. I put an alert just before the call to the trafficLightsOnDueDate.js, and it fired, but the alert I put just after the call did not fire. I checked the path to the java source (I’m using src=”/Javascript/trafficLightsOnDueDate.js”, because I put the Javascript library right below our site node) and it seems to be right. I put the jquery-1.3.2.min.js script in the library. I have the required columns in my list view, and the calculated column seems to be calculated correctly — but it doesn’t translate to the lights — that javascript is probably not running for me, I expect. This is the first time I’ve tried to run a javascript, so I did send off an email to the IT people, and asked if perhaps I don’t have permission to run a script?
The other thing I’m questioning is, is the AllItems.aspx file the correct file to be editing? You refer to the “list form”, but I’m not quite sure what that is.
Hi,
Double check that you have saved the script in the correct format (hover over the script and click “view source”, highlight and copy – paste in notepad and save).
To check that it is referred correctly, you can try to alert the function name like this (in the CEWP-code):
Both should give you a dialog box with “function”. If you get “undefined”, the scripts are not referred correctly.
Alexander
Hello Alex,
I got the same problem Jillian described. But even after putting the full URL of the site: “http://team.eu.enet/sites/ARQUIVOSQRT8D/NCRsCliente/Javascript/jquery-2.1.4.min.js”. It still not working, I am getting “undefined”.
Any idea why?
Rgs,
Geraldo.
Hi,
If you get “undefined” your path tot the file is incorrect, or the file is corrupt. Try pasting the “src” for the script in a new browser window address bar. If it is correct, you should get a download prompt (or the script should show in the browser window).
Alexander
As you suspected, I get “undefined” in the alert(typeof(…)) dialog boxes. But I did save the spripts in the manner that you described. I question the way I made the Javascripts library, however. I created it as a shared document library, but when I was prompted to choose which type of document template to use, I selected “None”, because the others did not seem appropriate (like Word document selections and such). So then the only way I was able to add documents to the library was to use “upload”, and upload the Notepad files from my machine to the server. Is there supposed to be an appropriate document template type for scripts that I should have chosen?
OK never mind! I finally got it. I put in the full path to the scripts source and it worked!! I must have made an incorrect assumption about our site path, and so got the wrong relative path.
Thanks so much for your help and for sharing your scripts.
This is great – I had been using workflows to update a status field with icons based on due date.
I would like to take this a step further…
Instead of the bullets, I used the words Satisfactory, Warning, Critical, and Past Due. I use these same terms to do some SharePoint charting using Fusion Charts.
Fusion Charts reads the value of the field by reading up the XML for the list.
The issue I have is that the actual field value for the calculated column is “Due Date: mm/dd/yyyy” – what I need it to be is “satisfactory”, ‘warning”, etc. so that I can chart it.
Is there anyway to populate an additional column with the text that is rendered in the calculated column?
Also, do you accept any PayPal support for your efforts/time/web hosting fees?
Hi,
I’m afraid this is not possible using this approach. While you can swap the bullets with the text, the values only “live” in the browser window. No changes are written to the list and therefore you cannot chart using this values.
Another note about this approach is the fact that it relies upon the users “locale computer time” to be correctly set.
Alexander
Gotcha – I sometimes fail to grasp that most of these javascript and CEWP type solutions are “presentation layer” only!
What is the script for the jquery-1.3.2.js?
Hi,
The current release is 1.4.2. You find it here:
http://jquery.com
Alexander
I would like to use this script on my sales site..I’m new to jquery and would like to use the traffic lights to signal if the sale goal is reached green – average yellow or below. How do I adjust the code to allow to a text field called score.
This script is for comparing dates only, take a look at Christophe’s HTMLcc
Alexander
I love this code! is there a way to replace the colored bullets with text? I need to be able to flag a task if today is more than 45 days past the assignment date. Instead of the red bullet, I’d like the field to state “Overdue”.
First time poster, long time reader :-).
I was wondering if this code could be modified to show the today’s date field. It seems a lot of code can show a comparison of a predetermined date to [today]. Is it possible to set up a jscript that will dynamically update to the local current time and date?
To be more specific with the last post, add a column that show’s today’s date and time to the calculation.
Hi,
I’m not quite sure what you mean. You cannot use todays date in a calculated column because the column value is “static” and updates only when the item is modified.
If this is not the answer to your question, please rephrase or send me some more information in an email.
Alexander
How hard would it be to change the code to change the background color of say the due date cell to yellow, red, green instead of adding bullets? I have 4 dates in each row that I need to color based on todays date.
Hi,
Not hard at all. This example is taken from the above code. Change from this
$(this).html("<div style='font-weight:bold; font-size:16px;color:green'>•</div>"); $(this).parent().attr('title',TooltipDueIn + DueByDays);to this
$(this).css({'background-color':'green'}).parent().attr('title',TooltipDueIn + DueByDays);Alexander
This code works good on my site. Thx for posting it.
Instead displaying colors is it possible to display only Days information? Like If its Due In it should just display Due In: 8 Days (no hours and minute) and if its Overdue also it should just display the number of days.
Thx again for posting this.
Regards,
Paavan
Hi Alex,
Looking through the site if there is a script that I can incorporate with this one to hide the Status column. I can remove the DueDate column from the view to work but as you mentioned the Status column needs to be there.
Is there a way to retain the color highlighting on the row but hide the status column?
Also would this work on two seperate due date fields?
Hello Alexander
just I want to ask you why you add Completed field (Yes/No) in the above list. as you know the built in task list come with Status field where you can define the status such as in-progress, on-hold, completed….etc.
I followed your steps and everything works find. but i don’t want to add unnecessary field in my list.
is there a way to just compare the due date with today and put the color accordingly.
This is an example, you can use whatever formula you want.
Alexander
I am really new feeling my way through this and having problems saving the trafficLightsOnDueDate.js. i get an undefined error. Can you step me through as i feel im missing something simple. Thanks
Where do you get this error? – when saving the file in the browser, or in SharePoint?
Alexander
HI Alexander. Thanks for the quick response. I am really new to Sharepoint and attempting to steam-roll my way through a solution for my team. I am seeing the error in Sharepoint after adding the line to test my references above. SharePoint seems to be not liking my reference to trafficLightsOnDueDate.js. I saved the code in Notepad as outlined above
Hi,
I need to see the actual error message.
Alexander
After adding the lines to test the CEWP script i get 2 prompts. the first say ‘function’ the second say ‘undefined’. im assuming this refes to the trafficLightsOnDueDate.js file. I created trafficLightsOnDueDate.js in Notepad by hovering over the script above, selecting view source code, then pasting this into notepad. I then uploaded to my site. Is that correct? Appreciatee your help and patience
HTML1113: Document mode restart from IE9 Standards to IE8 Standards
AllItems.aspx
SEC7115: :visited and :link styles can only differ by color. Some styles were not applied to :visited.
AllItems.aspx
SCRIPT1004: Expected ‘;’
trafficLightsOnDueDate.js, line 112 character 39
SCRIPT5007: The value of the property ‘trafficLightsOnDueDate’ is null or undefined, not a Function object
AllItems.aspx, line 905 character 1
Thanks
Sorry i sent the previous post in error. These are the errors :
SCRIPT5009: ‘_spBodyOnLoadFunctionNames’ is undefined
AllItems.aspx, line 158 character 1653
SCRIPT5007: The value of the property ‘GetProjectTaskLaunchInstalled’ is null or undefined, not a Function object
AllItems.aspx, line 178 character 1
SEC7115: :visited and :link styles can only differ by color. Some styles were not applied to :visited.
AllItems.aspx
SCRIPT5007: The value of the property ‘ExecuteOrDelayUntilScriptLoaded’ is null or undefined, not a Function object
AllItems.aspx, line 791 character 1
SCRIPT1004: Expected ‘;’
trafficLightsOnDueDate.js, line 112 character 39
SCRIPT5007: The value of the property ‘trafficLightsOnDueDate’ is null or undefined, not a Function object
AllItems.aspx, line 905 character 1
Thanks
Hi,
This messages does not look like they are related to the traffic lights solution. Does the page where you want this solution activated use the default master pager? (if it is not created in SPD it does).
Which version of SharePoint are you using?
Alexander
Hi Alexander, I am not using SP. We are running SP 2010. Thanks again
Sorry for the late reply. Unfortunately there is not much I can do for you as the errors you posted is not related to any of the functions from this solution.
Alexander
Hi Alexander,
Great idea how to work with today’s date.
However it’s not working in my situation. I’ve a (dashboard) page with a document library web part. I’ve created the calculated column Status (named ‘Alert’ in my solution) and it properly displays “Due: “.
But the script (placed in a CEWP on the same page) ain’t replacing this text by bullets, am i missing someting? Doesn’t work this solution on document libraries?
Additional info:
I’ve dived into the script running on this Dashboard-page, and I found these errors:
SCRIPT1004: Expected ‘;’
trafficLightsOnDueDate.js, line 113 character 39
SCRIPT5007: The value of the property ‘trafficLightsOnDueDate’ is null or undefined, not a Function object
Dashboard.aspx, line 1444 character 1
line 113:
wrapDiv.innerHTML=”<TABLE><TBODY id=”tbod”+groupName+”_” isLoaded=””+isLoaded+””>”+htmlToRender+”</TBODY></TABLE>”;
line 1444:
trafficLightsOnDueDate();</script>
Can you help me fix these errors?
Hey Bertrik, did you manage to make it work?
Hi,
It looks like you might have a corrupted file. Can you try copying the code again?
Alexander
Hi!
I recopied the code for trafficLightsOnDueDate.js
Still same errors.
I’ll remove the function for grouped views, because those won’t be used on these pages.
I’ll let you know…
Solved!
It appears the issue was on function ExpGroupRenderData
Hmm… now the difference between due date and today is not correct for every row…
For some rows it’s correct, but others rows say it’s today (around) 18 Sep. 2015. How is that possible?
Hard to tell. Ensure you have set the correct data format in the setup.
Alexander
Hello Alex,
I am getting some difficulty to implement your code on my SPS site. I followed all the steps and the only difference is the jquery version, which is the update version 2.1.4.
The Status column is giving the “Due: MM/DD/YYYY” as expected, but the java code seems to not work.
Any idea?
Rgs,
Geraldo.
Sper Super Super amazing code!Thank you Alexander for sharing this. I am new to sharepoint an java but managed to run this code howeverthe IT department is restricting me from using the jquery-1.3.2.min.js for different reasons. Please Alexandar tell me how can i run the code without calling the jquery. when i removed it from the CEW Pthe code dint work. your help is greatly appreciated
Hey, anyone can help me with my question above??
Hi,
The code is created to run with jQuery and will not run without. You don’t need to use the old v1.3.2 version if this is the problem, but you need jQuery.
As mentioned in the email it can be rewritten to use “plain” javascript, but I don’t have time to rewrite it for you .
Alexander