All posts by Alexander Bautz

Compare DueDate with Today in list view

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:
IMG

You can filter on the “Status-column”:
IMG

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”):
IMG

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:
IMG

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”:
IMG

Thats it!

Alexander

Welcome to my blog!

Welcome!

You find newly added posts on the right side of the screen. All previously posts are found in the “Archives”, and you can search for keywords in the top right corner of the front page.

How to post code in comments
How to troubleshoot when the scripts does not work
How to use these scripts in a customized form

I have added a Copyright and disclaimer notice here.

Requests are posted in the Request’s post.

Regards
Alexander