locked
Javascript to colour code List items based on date parameters RRS feed

  • Question

  • Hello there, wondering if anyone knows how to colour code based on date parameters.

    date is more than [today]-7 = green

    date is less than [today]-7 = red

    date is less than [today]-7 but is more than [today]-14

    thank you!


    • Edited by brice235i Monday, March 14, 2016 1:32 PM
    Monday, March 14, 2016 1:32 PM

Answers

  • Hi brice235i,

    There is some code (“style”) missing in the script above when I copy and paste the script from the test environment to the forum.

    The correct formula is:

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"d/mm/yyyy")&"</span>"

    Please give it a try.

    For the date format, you can follow Alex’s suggestion, change “m/dd/yyyy” to “d/mm/yyyy”.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.



    Wednesday, March 16, 2016 1:25 AM

All replies

  • Hi brice235i,

    We can apply color coding using OOB calculated column in SharePoint.

    Here are the steps:

    1. Create a column named “Today”. The type doesn’t matter here, just click “OK”.

    2. Create a calculated column (called “color”), and add the following formula in the formula area:

    ="<span color:"&IF(([date]+7)>=Today,"green",IF(And(([date]+7)<Today,([date]+14)>Today),"orange","red"))&""">"&Title&"</span>"

    In this test, date is more than [today]-7 = green, date is less than [today]-7 but is more than [today]-14=orange. Else, =red.

    Return as Number/Currency/Date and Times.

    3. Delete the “Today” column to make SharePoint get the default [Today] current date. If you need to edit formula, you need create “Today” column again, then edit formula.

    For more details about use [Today] in calculated column, you should refer:

    https://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/

    The screenshot of testing result for your reference:

    If you want to color coding SharePoint list using JavaScript, you can refer the following article. And you need to get the current date and compare the date in JavaScript.

    http://techtrainingnotes.blogspot.jp/2008/11/sharepoint-color-coding-sharepoint.html

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, March 15, 2016 5:07 AM
  • Thank you so much Linda!  Really appreciate it.  I pasted this code:

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&Title&"</span>"

    It worked as expected.  However, I hoped to colour code the Engagement Date as the title is irrelevant - actually ideally the color column would contain a traffic light similar to the status list indicators.  I changed "&Title&" to "&[Engagement Date]&" and set the data type returned as Date and Time.  Unfortunately I've ended up with the below.  Are you able to see where I've gone wrong? 

     Thank you Linda.


    • Edited by brice235i Tuesday, March 15, 2016 7:04 AM
    Tuesday, March 15, 2016 6:42 AM
  • Hi brice235i,

    For date, please use “&TEXT([date],"m/dd/yyyy")&” instead.

    This “Text” function will convert date format into “m/dd/yyyy” format.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, March 15, 2016 7:20 AM
  • Hi Linda, I got an error for this

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">“&TEXT([Engagement Date],"m/dd/yyyy")&” </span>"

    Tuesday, March 15, 2016 8:03 AM
  • Hi brice235i,

    The issue is caused by there is an incorrect double quotes format in it. Use (") instead of (”).

    Please use the following formula:

    ="<span color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"m/dd/yyyy")&"</span>"

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Tuesday, March 15, 2016 9:31 AM
  • Almost Linda, but now I've lost the colour and the date is in US format, and still some syntax problem.  Thanks for helping. 


    • Edited by brice235i Tuesday, March 15, 2016 10:58 PM
    Tuesday, March 15, 2016 10:57 PM
  • US date format is easily fixed by changing 'm/dd/yyyy' to 'dd/mm/yyyy'. 

    The formatting issue is probably still a result of incorrect quotation marks. This is why all code snippets should be in the code tool.

    This code is probably broken but shows off the code tool:

    ="<span color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"m/dd/yyyy")&"</span>"

    Tuesday, March 15, 2016 11:20 PM
  • Hi Alex, thank you. The date is fixed but I've lost the colour, and still there are extra characters. I tried removing various combinations of quotes, but it broke each time.

    ="<span color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"dd/mm/yyyy")&"</span>"

    Wednesday, March 16, 2016 1:25 AM
  • Hi brice235i,

    There is some code (“style”) missing in the script above when I copy and paste the script from the test environment to the forum.

    The correct formula is:

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"d/mm/yyyy")&"</span>"

    Please give it a try.

    For the date format, you can follow Alex’s suggestion, change “m/dd/yyyy” to “d/mm/yyyy”.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.



    Wednesday, March 16, 2016 1:25 AM
  • Very close now Linda!  I have the colour back, but still have an erroneous prefix 1/04/2016">

    Wednesday, March 16, 2016 1:36 AM
  • Hi brice235i,

    For this issue, please make sure you are using the correct punctuation marks in the formula. Re-typing the formula for the calculated column and check if this is caused by the wrong punctuation mark.

    Since I cannot reproduce this issue, please provide the complete formula of the calculated column for checking.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, March 16, 2016 2:10 AM
  • Here is the code Linda

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"d/mm/yyyy")&"</span>"

    ="<span style=""color:"&IF(([Engagement Date]+7)>=today,"green",IF(AND(([Engagement Date]+7)<today,([Engagement Date]+14)>today),"orange","red"))&""">"&TEXT([Engagement Date],"d/mm/yyyy")&"</span>"

    Wednesday, March 16, 2016 2:31 AM
  • Hi brice235i,

    I find the cause. The extra characters display because you select the “Data and Time” type to return the data. The “Date and Time” type does not parse the data correctly.

    Please use “Number” or “Currency” type for the calculated column.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Marked as answer by brice235i Wednesday, March 16, 2016 3:49 AM
    • Unmarked as answer by brice235i Wednesday, March 16, 2016 3:50 AM
    Wednesday, March 16, 2016 2:59 AM
  • Brilliant.  Thank you Linda!  Really appreciate your help :) 
    • Marked as answer by brice235i Wednesday, March 16, 2016 3:49 AM
    • Unmarked as answer by brice235i Wednesday, March 16, 2016 3:49 AM
    Wednesday, March 16, 2016 3:49 AM