General

Re: SharePoint Calculated Column with HTML Query (Re-Post)

Joey_86 — Tue, 27 Nov 2012 02:45:47 GMT

Hi Patrick,

Thanks for your reply to this post, but I'm actually working on a SharePoint calculated column formula. The above rules relate to an InfoPath form, do you know how I can condense my formula to achieve what I need in a SharePoint calculated column?

Re: SharePoint Calculated Column with HTML Query (Re-Post)

Patrick Halstead — Sat, 24 Nov 2012 19:34:40 GMT

Hi Joey_86 and thanks for posting so much,

I did a webinar on September 27th with some samples for calculating overdue colors (see Qdabra's YouTube channel for the video). You can do this all with rules and the translate xpath command to do conditional formatting.

Shade Red rule has a format set to orange pastel if the following condition is true: translate(my:DueDate, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "")
Shade Yellow rule has a format set to yellow pastel if the following two conditions are true: translate(my:DueDate, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(), 30), ":-T", "") and
translate(my:DueDate, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "")
Shade Green rule has a format set to green pastel if the following condition is true: translate(my:DueDate, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 30), ":-T", "")

Make sure you put them in the order above.

Best of luck.

SharePoint Calculated Column with HTML Query (Re-Post)

Joey_86 — Mon, 19 Nov 2012 02:20:58 GMT

Hi All,

I'm just having a bit of trouble creating a calculated column query for what I need, basically I have the following columns:

- 'Current Status' with the following selections:- In Progress, On Hold, Complete & Withdrawn/Rejected

- 'Target Age': This is a calculated column which calculates the number of days between the created and target date of an item which also includes negative numbers (

- 'Overdue': This is also a calculated column identifying the following 'IF([Target Date]="","Blank",IF([Target Date]<=Today,"Yes",IF([Target Date]>=Today,"No")))'

- 'Status Indicator': This is the calculated I'm having issues with which will display a HTML image marker based on the IF statements

Now this is the problem I'm having, I need the following scenario's built into the IF statement for the calculated column

- If the 'Current Status' = 'In Progress' & 'Overdue' = 'No' (Green Marker)

- If the 'Current Status' = 'Complete' & 'Overdue' = 'No' (Green Marker)

- If the 'Current Status' = 'On Hold' & 'Overdue' = 'Yes' (Yellow Marker)

- If the 'Current Status' = 'On Hold' & 'Overdue' = 'Blank' (Yellow Marker)

- If the 'Current Status' = 'In Progress' & 'Overdue' = 'Yes' & 'Target Age' < 0 (Red Marker)

- If the 'Current Status' = 'In Progress' & 'Overdue' = 'Blank' & 'Target Age' < 0 (Red Marker)

- If the 'Current Status' = 'Withdrawn/Rejected' & 'Overdue' = 'Yes' (Grey Marker)

- If the 'Current Status' = 'Withdrawn/Rejected' & 'Overdue' = 'Blank' (Grey Marker)

This is what I have tried already but hasn't seemed to work for the test items I specified: 'IF([Current Status]="In Progress",IF([Overdue]="No","<DIV><IMG SRC='/Pictures/Marker/green.png'></DIV>",IF([Current Status]="On Hold",IF([Overdue]="Yes","<DIV><IMG SRC='/Pictures/Marker/yellow.png'></DIV>",IF([Current Status]="On Hold",IF([Overdue]="Blank","<DIV><IMG SRC='/Pictures/Marker/yellow.png'></DIV>",IF([Overdue]="Yes","<DIV><IMG SRC='/Pictures/Marker/red.png'></DIV>"))))