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>"))))