3/02/2011

SharePoint, JavaScript and Dates

 

How does your SharePoint site display a date?

The answer is… “it depends.”  Create a new task and schedule it for January 3rd, 2012. Now display the task list and see how the date is displayed.

Do you see this image  or this image

You would see the first if you had your SharePoint preferences (Regional Settings) set to a country where they enter dates as dd/mm/yyyy, such as France.

To play with the regional settings, click the Welcome menu at the top right of a SharePoint page and click My Settings. Then click My Regional Settings, uncheckmark “Always follow web settings” and pick a locale.

image  

image

image

 

So what’s the problem?

If you are writing JavaScript code that needs to work with the date you may get the wrong date! As an example, if you wrote some JavaScript to read the Due Date from a task and changed the color or font for past due tasks you would probably use the Date.parse method to test the date:

var colDueDate = 7; // list column with Due Date
if (d.getTime() >= Date.parse(row[i].parentNode.childNodes[colDueDate].childNodes[0].innerHTML) )
{
  row[i].parentNode.style.backgroundColor='red'; // set the color
}

In this example, if today’s date is February 15th 2012 and the Due Date is March 1st 2012 then Date.parse would show the task as past due for the French users and not past due for the United States users.

JavaScript date references:

http://msdn.microsoft.com/en-us/library/ff743760.aspx

http://www.w3schools.com/jsref/jsref_parse.asp

 

The fix?

Display the date in a format that Date.parse likes. JavaScript first attempts to parse the date as an ISO formatted date. It then tries a few other rules, but none of those support dd/mm/yyyy. What we need to do then is deliver a date from SharePoint that will always work with JavaScript, and one that always works is yyyy/mm/dd. You could just pick a locale that uses that format, but your users are not likely to be happy with that.

What works in my projects is to add a calculated column to my list that generates yyyy/mm/dd format, hide that column in the display and use that column for my JavaScript date math.

  1. Add a new column to your list and give it a name like DueDateYYYYMMDD
  2. Add a formula:
             =YEAR([Due Date])&"/"&MONTH([Due Date])&"/"&DAY([Due Date])

    image 
  3. Save your changes
  4. Add this new column to your view
  5. Add some tasks with a due date and verify that both Due Date and DueDateYYYYMMDD have the same dates, just formatted differently
  6. Go to your Welcome menu, click My Settings, My Regional Settings and change your locale to France and confirm that while the Due Date column has changed, the DueDateYYYYMMDD is still the same

Make it do something useful

The JavaScript below is an update to the “SharePoint: Past Due / Late Tasks in a Task List” article. The code now works regardless of the SharePoint or user locale (date format) settings.

image

 

<script type='text/javascript'>
// customization trick from TechTrainingNotes.blogspot.com
 
var colDueDate = 6;  // This is the column with the due date in the YYYY/MM/DD format
var colToHide  = 6;  // This is the same column as above if you want to hide the custom date format
var colPastDue = -1; // optional column to display a custom JavaScript set message
var tableName = "Tasks"; // (from summary="")
 
// find the table for the list
var tables = document.getElementsByTagName("TABLE");
var table
for (x in tables)
{
  if (tables[x].summary == tableName)
  {
     table = tables[x];
     break;
  }
}
 
// hide the column with the special date (optional)
if (colToHide > -1)
{
  var TRs = table.getElementsByTagName("TR");
  for (x in TRs)
  {
 
    var toHide = colToHide;
    if (TRs[x].innerHTML)
    {
      if (TRs[x].innerHTML.toString().toLowerCase().indexOf("iframe") > -1 )
      { toHide += 1; }; // fix for 2007
 
      try {
      } catch (e) {}
      if (TRs[x].childNodes && TRs[x].childNodes.length >= toHide)
      {
        TRs[x].childNodes[toHide].style.display="none";
      }
    }
  }
}
 
// Color code the row based on a date calculation
var TDs = table.getElementsByTagName("TD");
var now = new Date();
for (i in TDs)
{
  if (TDs[i].className=="ms-vb2") //find the TDs styled for lists 
  {
    //find a row with a key phrase
    if (TDs[i].innerHTML=="Not Started"
         | TDs[i].innerHTML=="In Progress"
         | TDs[i].innerHTML=="Waiting on someone else" )   
    {
      // is this row (task) past due?
      if (now.getTime() >= Date.parse(TDs[i].parentNode.childNodes[colDueDate].innerText) )
      {
        // set the color
        TDs[i].parentNode.style.backgroundColor='red'; 
        
        // optionally display a message in another empty column
        if (colPastDue > -1)
          TDs[i].parentNode.childNodes[colPastDue].innerHTML='Past due!'; 
      }
    }
  }   
}
</script>

 

.

14 comments:

JLB said...

I built a help desk system using lists. Trying to find code that will insert the date (Completed Date) when Status changes to "Completed"

Mike Smith said...

JLB,

Create a simple SharePoint Designer workflow to run on change and have it check the Status field = Completed and Completed Date = blank and then set the completed date field

Mike Schwier said...

Will this work for an issue tracking list as well?

Do i need to change the table name in the javascript?

Mike Smith said...

Mike,

It should work with just about any list.

Mike

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.

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.

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.

SVS 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.

SVS 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...

Hi Mike,

Thanks for posting this. I'm struggling to get it to work. I am very much a beginner. Do i need to insert the table name anywhere?

Mike Smith said...

Anonymous,

What are you wanting to do? Highlight past due tasks? If so see: http://techtrainingnotes.blogspot.com/2009/03/sharepoint-past-due-late-tasks-in-task.html

Mike

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.