1

I need to format rows in a list, based on the due date and status.

If a task is not completed and there is still more then 7 days time it should be green. If a task is not completed and there are 3 days or less time it should be orange. And if a task is not completed and there the due date = actual date or the actual date is greater then the due date the row has to be red.

Now it is easy to do this with basic rules, but how can I implement the 7days and 3 days rule into

ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@DueDate))) > ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))) and $thisNode/@Status != 'Abgeschlossen'

I have tried to add '-7' just before the last closing paranthesis at @DueDate but I couldn't get it working.

Kind regards.

2

If your status shows green where there is more than 7 days and shows orange when there are less than or equal to 3 what happens between 4 and 6?

I would do this by creating a column in your list with this a calculated field formula; you can then hide the column from views and apply conditional formatting based on it (or show the column as the status flag):

=IF([DUEDATE]<=TODAY(),"RED",IF([DUEDATE]<=TODAY()+3,"ORANGE","GREEN"))


EDIT

For German Locale use this formula instead:

=IF([DUEDATE]<=TODAY();"RED";IF([DUEDATE]<=TODAY()+4;"ORANGE";"GREEN"))

It looks like German locale adds the dates differently so you need to do +4 instead of +3

  • This formula contains syntax-errors. Shouldn' it be [Column]? And do I need to translate the method TODAY() into German, if my siteCollection is a German sc? – Marco Jun 20 '13 at 13:07
  • Wierdly, when I tested this before posting my answer I did it as [Column] and SP removed the square brackets (which I didn't spot before I pasted it over), so it's not an error, but I'll put the brackets back in for good measure. I changed my locale to German and it still worked, and I can't find any mention of issues with TODAY() on German collections, but it'll be easy enough to test. – CLockeWork Jun 20 '13 at 13:14
  • Ok, of course I changed the DueDate column to the German term, and tried it with square brackets and without in all possible variations. I am running into an syntax error every single time. – Marco Jun 20 '13 at 13:27
  • I looked back at my formula once I'd changed locale and spotted that in German the comma is replaced with a semi colon. I've updated my answer. – CLockeWork Jun 20 '13 at 13:37
  • There is no syntax error any longer, but a logical one. Every item, which is not over due is green; even the one with -1 day -2 and -3 which ought to be orange /edit: changed +3 to +4 and now it works with days up to 3 days left. Don't know why. Just that it works ;) Thank you very much! – Marco Jun 20 '13 at 13:42
0

This is an old question, but I needed to accomplish approximately the same thing (minus the status) without using a calculated column (the due date in my list is pulled from a lookup to another list). There may be a more elegant way, but the following xsl code worked perfectly, and could easily include the status check.

I created variables for red (7 days) and purple (21 days) date ranges (my list uses red, purple, and blue) and a variable for days which converts the ddwrt:DateTimeTick difference between the due date and today into days. It then checks whether that number of days is <= $red, > $red and <= $purple, or > $purple, and formats accordingly.

<xsl:attribute name="style">
  <xsl:variable name ="red">
    <xsl:value-of select ="7"/>
  </xsl:variable>
  <xsl:variable name ="purple">
    <xsl:value-of select ="21"/>
  </xsl:variable>
  <xsl:variable name ="days">
    <xsl:value-of select ="(number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($thisNode/@WP_x0020_ID_x003a_LBE_x0020_Date)))) - number(ddwrt:DateTimeTick(ddwrt:GenDisplayName(string($Today))))) div 864000000000"/>
  </xsl:variable>
  <xsl:if test="$days &lt;= $red"
   ddwrt:cf_explicit="1">
    font-weight: bold; background-color: #FF5050; color: #000000;
  </xsl:if>
  <xsl:if test="$days &gt; $red and $days &lt;= $purple"
   ddwrt:cf_explicit="1">
    font-weight: bold; color: #000000; background-color: #CCADEF;
  </xsl:if>
  <xsl:if test="$days &gt; $purple"
   ddwrt:cf_explicit="1">
    font-weight: bold; color: #000000; background-color: #9CCDEE;
  </xsl:if>
</xsl:attribute>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.