conditional formatting with advanced rules

Question

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.

Answer 1

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

Answer 2

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>