Compare date with today: status indicator in list view

Change log
May 03. 2013
Changes in v1.3:
Changed how the dateformat is read from “settings.dateFormat” to handle formats with four digit yyyy.

10.11.2012 Updated to v1.2 to add mouseover displaying days, hours and minutes. This update also includes the time portion of the “DueDate” in the calculation. Please note that the calculated column code must be changed if you want the time taken into account.

The CEWP code has not changes since the last release, but if you want to override the English text used in the mouseover, include this in your CEWP code (or modify in the file “HighlightRowByDueDate.js”:

var prefix = ["Due in ", "Overdue by "];
var tDays = ['day','s'];
var tHours = ['hour','s'];
var tMinutes = ['minute','s'];

30.11.2011 I had a bug that made it work for grouped views only. This is fixed in v1.1.
Only the code for the file “HighlightRowByDueDate.js” needs updating. Sorry for the inconvenience.

The first solution i posted in this blog was the Compare DueDate with Today in list view.

I have redone the solution and added support for SharePoint 2010 grouped views.

I have NOT included the mouseover “overdue by” text. If there is a demand for it, i could add it back in.

IMG

How to use

You put a CEWP below the list view web part. The actual code has been put in a separate file for use in multiple lists. The “function call” itself is placed in a CEWP below the list view web part.

The code for the file “HighlightRowByDueDate.js” is found here

Download the code and put in a text-file and place it in a shares document library (all users must have read access), or in a folder created in SharePoint Designer.

You should also put the CEWP-code in a text file in the same location and use the “Content Link” field in the “Edit web part” panel of the CEWP. This is most important in SharePoint 2010.

CEWP code

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/HighlightRowByDueDate.js"></script>
<script type="text/javascript">
/* Hightlight row by due date
 * ---------------------------------------------
 * Created by Alexander Bautz
 * alexander.bautz@gmail.com
 * https://spjsblog.com
 * ---------------------------------------------
 * This script depends upon functionality provided by jQuery - http://jquery.com
 * ---------------------------------------------
 * v1.2 - 10.11.2012
*/

function init_highlightOverdue(){
	var settings = {greenDays:'', // Green color for everything not yellow or red
					greenColor:"<img src='/_layouts/images/imnon.png' border='0'> ", // green image
					yellowDays:1, // Yellow color one day after due date has passed
					yellowColor:"<img src='/_layouts/images/imnaway.png' border='0'> ", // yellow inage
					redDays:2, // Red color two days after due date has passed
					redColor:"<img src='/_layouts/images/imnbusy.png' border='0'> ", // red image				
					dateFormat:'mm/dd/yy',
					identifierText:'Due:',
					replace:true};
	highlightOverdue(settings);
}
</script>

NOTE

You must change the “src” in the script tag referring the script “HighlightRowByDueDate.js” to reflect your local copy.

Parameters explained

  • greenDays: either an empty string ” to indicate “green for everything not yellow or red” or an integer (positive or negative) to indicate the “offset” to highlight green.
  • greenColor: The image or text / HTML yo mark the items as “green”.
  • yellowDays: The number of days (positive or negative) to offset the yellow marker.
  • yellowColor: The image or text / HTML yo mark the items as “yellow”.
  • redDays: The number of days (positive or negative) to offset the red marker.
  • redColor: The image or text / HTML yo mark the items as “red”.
  • dateFormat: The date format used in your calculated column (see details below).
  • identifierText: The lead-in text in the calculated column. This value is used to identify the cells to “treat”.
  • replace: true or false – should the “image” replace the text in the cell, or prepend to the value.

The calculated column

The “trigger” for the script is the lead in text in the calculated column – this text must match in the calculated column AND in the attribute “identifierText” in the settings object in the CEWP.

Note the text “Due:” in the calculated column in this example:
IMG

Note: The calculated column must be in the view to make the solution work.

Let me know if something is unclear

Alexander

41 Comments on “Compare date with today: status indicator in list view”

  1. I am a quiet Alexander Bautz fan. Thanks for the work you are doing. I work at an international children’s charity. Your charting and other code makes a big difference in presenting data to our decision makers.

    2. @Pat – I work for an NGO and tried to link up some NGO people working on SharePoint – tried to do that with twitter and other social media – but most told me they were willing to aswer my questions on the phone (as if I had any, I just wanted to connect) and did not see any value in a LinkedIn group or anything like that ..

      So here I try again – follow me on twitter @nicodejong and we can see from there.

      Alexander – thanks for letting me use your comments to connect with a potential peer.

  2. Pingback: Sharepoint Updates November-29-2011 | SDavara's Sharepoint Knowledge Center

  3. HI Alex,

    Trying this out, but having a problem. I am using WSS 3.0. I have added the js and CEWP code files to my usual location, updated the source links to point to my js file, added a calculated column to my task list and updated the formula to point to my Status & Due Date columns. Then added the CEWP and pointed the content link to the CEWP file.

    I get Due:30/11/11 in the “color coding” column but it doesn’t change it to the image. replace: is set to true and dateFormats match.

    Any ideas?
    Cheers
    Brian

      1. Send me some screenshots of the setup (CEWP, calculated column and list view in edit mode).

        Alexander

    2. I had this same problem

      My calculated field looks like this
      =IF(Status=”Open”,”Live:”&TEXT([Live Date],”mm,dd,yyyy”),””)

      and my CEWP looks like this
      dateFormat:’mm/dd/yyyy’,
      identifierText:’Live:’,
      replace:true};

      My solution was I had mm,dd,yyyy instead on mm/dd/yyyy just needed to update the , to /. Hope that helps in your solution.

  5. Hi,

    i Want to calculate the number of days remaining of each and every task by comparing the due date with today, store them in a new field and color the data as per the number of days remaining, for example, the data will

    Due in 1 Days or less -> Yellow
    Overdue -> Red
    Due in 5 days or more -> green

    i had tried to find out the solution but struct, please help me in it.

    Thanks,

  6. Shaikh, I am trying to implement the same solution:
    1. Show the number of days from Expiry Date to Today’s date
    2. Show icons based on pre-defined red, yellow and red days

    Could you please share you code?

  7. Are you supposed to add a “Color coding” field because I’m not getting any colors anywhere? Just the duedate showing up as “Due: 8/9/2012” in my calculated field “Status”.

    Thanks

  8. Ok, so you are supposed to add a “color coding” field. My browser wasn’t showing that image. Sorry.

    However, I’m still not seeing any colors anywhere.

  9. Hi Alexander,

    Fantastic post, works like a charm. Thank you!

    I just have one question. I noticed that after a period of time viewing a page, the status icons revert to the Due:text. I have to refresh the page to get the icons back.

    Is this related to the timeout loop in the script? If so , what do I need to change to extend the timeout period?

    Many thanks
    Dan

  10. Hi Alex,

    How do I get the HighlightRowByDueDate.js file? When I click the link, the only files in the folders are .txt files but when I click them it just opens up a blank screen.

      1. For now you can click the file, then right click the page and select view source.

        Alexander

  11. Shaikh/Alla,

    Would it be possible to share the modified code? I would like to create a tasker with an indicator based on the number of days before it is due as well. Thank you so much for any help you can provide.

    Scott

  12. Hi all,
    The code is updated with support for the time portion of the date, and I have added mouseover with days, hours and minutes.

    Alexander

  13. Dear Alexander,
    Thanks a lot for your smart code.
    Nevertheless, I have a little problem. I no longer have access to “List tools” to access the Datasheet view for example.
    It seems that there is a conflict between JQuery and MS Ajax, but not sure.
    Do you have any idea about this issue?

  14. Alex,

    I’ve done my best in attempting to recreate the identical list with corresponding folders and .png (layouts, images, imnon.png, etc.), as a TEST.

    What I have managed to create is a list that displays a date (for example – “Due:12/25/2012”), mimicking the actual content of the DueDate column. There is no image displayed.

    I did modify the calculated “Color coding” formula to display…

    =IF(Status”Completed”,”Due:”&TEXT(DueDate,”mm/dd/yyyy”),””)

    In addition, (this might help in isolating the issue) the CEWP beneath the list view displays…

    “; c.parentNode.replaceChild(b.firstChild.firstChild, c); if(typeof(_fV4UI)!==’undefined’){ customTimeoutLoop(“tbod” + a + “_”); }else{ init_highlightOverdue(); } } $(document).ready(function(){ init_highlightOverdue(); });

    Despite hiding the CEWP, it still displays on the site.

    Your thoughts please!

    1. Hi, You must insert the code as HTML and not as text. Put the cursor where you enter text, then see the banner for “edit html”. Alexander

  15. Alex,

    Thanks it is working, for the most part, with only one issue to resolve. I still have the following appearing on the page below the list.

    “; c.parentNode.replaceChild(b.firstChild.firstChild, c); if(typeof(_fV4UI)!==’undefined’){ customTimeoutLoop(“tbod” + a + “_”); }else{ init_highlightOverdue(); } } $(document).ready(function(){ init_highlightOverdue(); });”

    Not entirely sure why. I have applied, what I thought were remedies but no success.

    Thank you for your help.

  17. Hey Alexander,

    Brill post but I am having a few issues – I have followed all the instructions and it is still not working! I have added the alert as per the comment above and I get the alert when I run the code outside of sharepoint but not inside.

    Any ideas?

    Cheers,

    MW

  18. Hi Alex,
    thank you for you solution.
    I’ve gor the similar as Dan in 2013. After setting a filter in any colmn in the current view the indicators disapears and the “Due: …” context appears. After deleting the filter and refreshing the site the colored indicators are back.
    Do you have an idea why?

    thanks
    dominik

    1. Hi,
      This has not been tested in SP2013. You might get it fixed if you add this code to the bottom of the CEWP:

      var trafficLightsViewTracker = "";
setInterval(function(){
	if(location.hash !== trafficLightsViewTracker){
		trafficLightsViewTracker = location.hash;
		init_highlightOverdue();
	}
},500);

      This is NOT tested and there is no guarantee it will work.

      Alexander

  19. Hi Alex,
    thank you for your reply.
    Seems to work. I will give you feedback after my tests.
    Is this a refresh or something like that?
    regards
    dominik

  20. I need some help regarding the sharepoint development, i am not aware of java coding, but i need a page where it would pop up a message or send automatic mails when the deadlines are close to the delivery dates also i need the same for software licenses, wherein it pops and indicates the expiry dates. Can you help me out ??

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.