3/04/2009

SharePoint: Past Due / Late Tasks in a Task List

 

Update: If you have users who display dates in formats other than MM/DD/YYYY you will find that the following will not work consistently. Here’s is some background on the date problem in SharePoint and JavaScript and how to fix it: http://techtrainingnotes.blogspot.com/2011/03/sharepoint-javascript-and-dates.html (includes replacement code for this article)

 

The code below and in the above link works with both SharePoint 2007 and 2010.

 

Here’s another JavaScript trick to deal with a common request, highlighting past due tasks in a SharePoint Task List.

Here’s a few non-JavaScript ways that this has been done:

  • Create a custom workflow (too much work and not portable, but it can also send reminder emails!)
  • Use a trick to add a calculated field using [Today] (only works when the trick is reapplied every day or each task is manually edited)

This example uses a bit of copy-and-paste JavaScript and requires the following:

  • A Content Editor Web Part to hold the JavaScript
  • Some JavaScript you can copy from below
  • An extra column to hold the past due message (optional)

The goal:

 

To add the extra column:

If you would like to display a message such as “Past Due” or display an image then you will need an extra column, preferably one your users cannot edit. A calculated column that displays a blank will do this.

  • Add a new column to your list (Settings, New Column)
  • Give it a title (any will do) such as “Past Due”
  • Make the column a Calculated column and set the formula to ="" (and equal sign followed by two quotes)

To add the Content Editor web part and JavaScript:

  • Display the task list view to color code (each view will need its own web part and JavaScript)
  • Add a Content Editor web part
    • Site Actions, Edit Page
    • Add a Content Editor web part and move it below the calendar web part
    • In the web part, click Edit, Modify Shared Web Part, and in the Appearance section change "Chrome" to "None".
  • Add the JavaScript
    • Click the Source Editor button
    • Type or paste the JavaScript (examples below)
    • Count the columns displayed in your task list to find the “Due Date” and “Past Due” columns. The Attachments column is column zero and the Due Date is typically column five (0,1,2,3,4,5).
    • Edit the JavaScript to set these two numbers:
      var colDueDate = 5;
      var colPastDue = 7; // set to -1 to ignore
    • Click Save, OK and Exit Edit Mode to see the results
    • If you would like to display an image in place of the text change this line:

      x[i].parentNode.childNodes[colPastDue].innerHTML='Past due!'

      to:

      x[i].parentNode.childNodes[colPastDue].innerHTML='<img src="/_layouts/images/ERRLG.GIF" />'

      and set your SRC to an image in your library or in the SharePoint install folder (“images”).

Making this work in views other than “All Tasks”

You probably will first test this in the “All Tasks” view and then switch to one of the other task views and mumble something about Mike’s hacks don’t work everywhere!

Well… each view page is, well… it’s own page. Check the URLs.

All Tasks: http://…../AllItems.aspx
Active Tasks: http://…../active.aspx
My Tasks: http://……/MyItems.aspx

Each page will need it’s own Content Editor Web Part with the JavaScript, the extra column will need to be displayed and the column numbers in the JavaScript adjusted.

As an example, here are the steps for the “Active Tasks” view:

  • Modify the view to display the “Past Due” column
  • Check your column numbers for the “Due Date” and “Past Due” columns
  • Add the Content Editor Web Part and the JavaScript as described above, adjusting the column numbers as needed.

Bugs? Issues?

  • You cannot sort the “Past Due” column as this data is not in SharePoint. It has only been added in the user’s browser. (Another plus for a workflow solution?)
  • Each new view created will need the CEWP and the JavaScript added.
  • Changing or reordering the columns in a view will require an update to the column numbers in the JavaScript.
  • As the CEWP is not visible, you will probably forget that it is there, and then wonder why the color highlighting no longer works after you modify the view!
  • As the CEWP is not visible, the next person to inherit your SharePoint site will have no idea how this works. (You do document everything you do in your site, right?)

The JavaScript:

<script type="text/javascript" language="javascript">

  // Stolen from techtrainingnotes.blogspot.com !

  // this routine needs to know:
  // column with the Due Date (attachment column is #0)
  //      (in the default task list Due Date is #5)
  // optional column to display the "past due" message
  //      (can be any "single line of text" column)
  var colDueDate = 5;
  var colPastDue = 7; // set to -1 to ignore

  var i=0;
  d=new Date() //current date/time

  var x = document.getElementsByTagName("TD") // find all of the TDs
  for (i=0;i<x.length;i++)
  {
    if (x[i].className=="ms-vb2") //find the TDs styled for lists
    {
      //find a row with a key phrase
      if (x[i].innerHTML=="Not Started"
           | x[i].innerHTML=="In Progress"
           | x[i].innerHTML=="Waiting on someone else" )
      {
        if (d.getTime() >= Date.parse(x[i].parentNode.childNodes[colDueDate].childNodes[0].innerHTML) )
        {
        x[i].parentNode.style.backgroundColor='red'; // set the color
        if (colPastDue > -1)
        x[i].parentNode.childNodes[colPastDue].innerHTML='Past due!' 
        }

      }

    }

  }
</script>

16 comments:

PaulT said...

Hi Mike,

This solution to highlight past due items is just what I have been looking for.

I followed the instructions but it is only doing the highlighting on some of the lines where the due date is in the past. It will work for some dates but not for others.

I am not a programmer - just an end user that administers a SharePoint site. However I can get the rough flow of your code

The only changes I did to your code was to update the column numbers (as required) and to change

if (x[i].innerHTML=="Not Started"
| x[i].innerHTML=="In Progress"
| x[i].innerHTML=="Waiting on someone else" )

to

if (x[i].innerHTML=="Current")

This is because in my list there is a column for ‘Doc Status’ that can have a value of either Current or History and I don’t want the ‘History’ lines to be highlighted.

The only other aspects I can think of that might have a bearing are:
• As I am based in Australia our date format is dd/mm/yyyy. Would that have any effect?
• The due date (i.e. “Review date”) field I am comparing to is itself a calculated column (this is by a formula that takes the “Date Reviewed” column and adds 12 months)
• The document list uses Content Types. However I can see any pattern to suggest this is a factor.

Have you come across this before? Any clues?

PS: I am using SharePoint 2007. Happy to send you a screen shot if you want – just let me know how.

PaulT

Mike Smith said...

Paul,

> As I am based in Australia our date format is dd/mm/yyyy. Would that have any effect?

That would be my first guess, if you had said that all of the highlighted rows where wrong.


Here's some ideas to check out:

- Are the correct rows more than x hours or y days overdue, while the errors rows are only hours overdue? (is there a pattern?) If so, two possibilities: 1) Is the calculated date column displaying dates and times, or just dates? 2) is server clock is a few hours off from the PC's clock (different times or time zone settings?)

- Date.parse(..) - The Date object is from JavaScript in your local PC and uses the PC's clock settings, while the date in the list is from the server. Are the server and the PC both in the same time zone? (a different twist on #2 above)

Let me know if you solve the problem or have any other questions.

Mike

PaulT said...

Hi Mike,

Sorry to bother you again. I am definitely not a spammer.

I would really like to use your solution. As per previous posts I am not a programmer. Over the weekend I noticed that some of the dates that previously were not showing as past due suddenly were. I realised that the current date had changed from July to August. I retested and I think I have figured out why the 2010 dates are not working – because they are being interpreted as mm/dd/yyyy rather than dd/mm/yyyy See my test dates below. However this explanation still doesn’t explain the 2009 dates.

Anyway, here are the tests I ran on 3rd August 2010.

Due Date
7/1/10. Works. If in mm/dd/yyyy this is 1 July 2010 – makes sense
8/1/10. Works. If in mm/dd/yyyy this is 1 August 2010 – makes sense
9/1/10. Does not work. If in mm/dd/yyyy this is 1 Sept 2010 – makes sense

8/2/10.Works. If in mm/dd/yyyy this is 2 August 2010 – makes sense
9/2/10. Does not work. If in mm/dd/yyyy this is 2 Sept 2010 – makes sense

8/3/10.Works. If in mm/dd/yyyy this is 3 August 2010 – makes sense
9/3/10. Does not work. If in mm/dd/yyyy this is 3 Sept 2010 – makes sense

7/4/10. Works. If in mm/dd/yyyy this is 4 July 2010 – makes sense
8/4/10. Does not work. If in mm/dd/yyyy this is 4 August 2010 – makes sense
9/4/10. Does not work. If in mm/dd/yyyy this is 4 Sept 2010 – makes sense

Do you have any suggestions on how I can fix this so it works with the dd/mm format? The link I previously sent discussed this problem, but the javascript code is way over my head.

Whilst the 2010 dates now have an understandable pattern, I am still stumped by the 2009 due dates. If the problem was the mm/dd/yyyy interpretation I expected it would only work up to the point where the first two digits where 12 (i.e. December) then stop working after that. However the pattern with the 2009 dates is:

Due Date
1/12/2009 through to 19/12/2009. Works
20/12/2009 through to 31/12/2009. Does not work.

1/11/2009 through to 19/11/2009. Works
20/11/2009 through to 31/11/2009. Does not work.

As a matter of interest I tried due dates of 1/11/2008 through to 30/11/2008 and 1/12/2008 through to 31/12/2008 and they all worked. Something to do with how the dates get converted in seconds? Just a wild guess.

PaulT

Mike Smith said...

PaulT,

The date format it most likely the problem. I did a Google on "javascript date problem australia" and it looks like a common problem and solvable. I on a tight deadline right now and won't be able to dig into for a few days.

Mike

Anonymous said...

Hi Mike

I am from France ,

I have the same problem as Paul,

I have to work on the Date : ( DueDate and Today ), because the month and the day are inversed , and Date.Parse return a false value.

---
var today_Year = d.getFullYear()
var today_Month = d.getMonth() + 1
var today_Day = d.getDay()
var today_str = today_Month + "/" + today_Day + "/" + today_Year
var today_int = Date.parse(today_str)

the same thing with DueDate , to create DueDate_int .


And after I can compare today_int and DueDate_int .

Many Thanks for your Blog , it is very useful .

Francky

Mike Smith said...

PaulT and Francky,

I have found a fix for the date problem. See:

http://techtrainingnotes.blogspot.com/2011/03/sharepoint-javascript-and-dates.html


Mike

killajoedotcom said...

What do i need to add to this script to allow for the rows turning yellow when the due date is 7 days from being due?

Mike Smith said...

killajoedotcom,

An interesting idea, and not hard to do. Check back in a day or two and I'll post the code for that.

Mike

Mike Smith said...

To set the row to yellow when 7 days before the due date, add a new "if" block. Count up three curly brackets from the bottom of the script and add the following above the third bracket...

if (Date.parse(x[i].parentNode.childNodes[colDueDate].childNodes[0].innerHTML) - d.getTime() < (7 * 1000 * 60 * 60 * 24) )
{
x[i].parentNode.style.backgroundColor='yellow'; // set the color
if (colPastDue > -1)
x[i].parentNode.childNodes[colPastDue].innerHTML='Past due!'
}

Nancy said...

Hi Mike,

Your blog is very helpful and I have learned alot from your posts. Thanks for your efforts. I was able to get the row to go red, no problems. I tried the extra code to get the row to go yellow if 7 days before it's due and my red row turned yellow. I will play with the code a bit and see if I can make sense of it. I added it just above the third curly brace from the bottom.

Anonymous said...

Hi,
I just want to change the color. And with the Standard-view it´s working. But with another, customised view it doesn´t (the view is on a second page with of course a seperate CEWPart). If i change the view to Standard on the same second Page, it´s working again.

Any ideas? Thank you very much. - Andreas

Mike Smith said...

Anonymous,

What was customized in the custom view? Some options like Styles and Grouping can really change the HTML that's rendered.

Mike

Anonymous said...

Hallo Mike,
I´m referring to my last post on october 19, 2012 :
I fixed it. The problem was, that if I made a custom view, the colorization of the rows didn´t work. It turns out, that in the settings of the custom view I checked "Collapsed" (because I want the group to be collapsed for a better overview) in the "Group by"-field, and with that, your Java-Script didn´t work anymore. As you check "Expanded" it works again.
The trick now is to check "Expanded" in the view and ad a bit of code to your script, that collapses the groups in the list again:

[CODE]
var y = 100; // Check for 100 groups

for (var j=0;j<y;j++)
{
ExpCollGroup('1-'+j+'_','img_1-'+j+'_');
}
[/CODE]

The script will check every single "group" and with ExpCollGroup() it collapses them.

Greets, Andreas

Dina Patel said...

I would like to hightlight a row depending on days it has gone past the due date. I have my past due date column etc, I just do not know the script to tell it to colour the row 'red' if past due is =>-1, gold/yellow if past due is =<5 and green if past due is =<10. I do not have designer and have to use calculated coliumns or CEWP. Please help, getting very confused reading all the different post online. Thank you. Dina

Anonymous said...

Mike,
It works reaaly well, but I am facing a weird issue. When i try to use it on some pages, the script works and color changes in IE but not in Chrome or Firefox and on some others it works for Chrome/Firefox but not on IE. Is there any different set of properites for each of these browsers? Our standar is to use IE and it is not working on some pages on IE. Thanks.

Anonymous said...

Mike,
It works reaaly well, but I am facing a weird issue. When i try to use it on some pages, the script works and color changes in IE but not in Chrome or Firefox and on some others it works for Chrome/Firefox but not on IE. Is there any different set of properites for each of these browsers? Our standar is to use IE and it is not working on some pages on IE. Thanks.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.