Sharepoint Notes - Calculated Fields - Displaying HTML in Calculated Fiels, Displaying Status Indicator Icons, Display Week Number, Calculate Overdue Status

Display HTML in a Calculated Column

Make sure you select Number rather than single line of text for the format of your calculated column.

Use Calculated Column to Display if a Task is Overdue

This formula displays True or False: =AND([Due Date]<NOW(),Status<>"Completed",[Due Date]<>"") To make your Overdue column display a KPI Icon you can do this: ="<div><img src='/_layouts/images/KPIDefault-"&IF(AND([Due Date]<NOW(),[Task Status]<>"Completed",[Due Date]<>"")=FALSE,0,2)&".gif'/></div>"

Display KPI Icon for Yes / No column

This formula worked for me: ="<div><img src='/_layouts/images/KPIDefault-"&IF(Approved=TRUE,0,2)&".gif'/></div>"

Display Week Number in Calculated Column

This formula worked for me: =INT(([Due Date]-DATE(YEAR([Due Date]),1,1)+7-WEEKDAY([Due Date],1))/7)+1

Resources

Here's a list of resources I used to get to grips with this stuff. Some of them I've ended up referring back to several times.

• Week Numbers in Calendar
• Color Coding: More Examples
• Using calculated columns to add color coding to your SharePoint lists
• Using calculated columns to write HTML
• SharePoint Calculated Column Formulas
• Sharepoint converting a date to a week number
• Week Number – One Approach
• ISO8601:2000 Standard Week Number calculation
• Calculating Europen week number in SharePoint (ISO 8601)
• Conditional Color Coded List
• Colour coding a SharePoint 2013 list with jQuery
• Look Up A List of Week Numbers
• Modifying calculated column formulas based on values selected in other metadata fields
• SharePoint 2010 Icons
• HTML in SharePoint Calculated Fields (display a link)
• Week Number – One Approach