Calculated formulas for tracking progress

Question

I would like to let my team know if their document is progressing on track. I have a start date and due date and a column that they must enter their progress %. In a tracking column I would like to write a calculated formula that somehow lets them know if its tracking OK, might be at risk, or is at serious risk of not being completed by the due date. Can anyone help me with a formula?

Answer 1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

---

Original answer:

This type of tracking would rely on todays date (how long till [Due Date] ) and can not be done with a standard Formula because a List Item has no clue about 'today'.
Although many blogs claim otherwise, you can not use Today in a Calculated Column Formula because those Formulas are only re-calculated when an Item is updated.

For a detailed explanation see: How to use Today and Me in Calculated column

Summary:

But your Browser knows what day it is (today) and can do date calculations, so it requires JavaScript (front-end) development.
There are 2 options:

• Use Client Side Rendering (column Due Date)
• Use a Calculated Formula (column DueFormula)

They achieve:

In the examples below I used less colors;
You can create a Range array as long as you want.

SharePoint 2013/Online : Client Side Rendering

New since SP2013 is the ability to use JavaScript to display values your own way
read: CSR development made easy

The basic code is:

function CSR_DueDate(ctx){
    var range='No Due Date,days left,days past,#f7a,-14,#fab,-7,#fda,0,#cf9'.split(','),
        color=3,//index number of the first color
        days=GetDaysAfterToday(new Date(ctx.CurrentItem[ctx.CurrentFieldSchema.Name]));
    while (Number(range[color+1])<days) color+=2;
    var html = isNaN(days) ? range[0]:"<div style='background-color:{0}'>{1} {2}</div>";
    return( String.format(html , range[color] , Math.abs(days) , range[days>0?2:1] ));
 };

(+ some 20 more lines of boilerplate CSR code)

JavaScript in Calculated Columns

You do not need seperate CSR files and JSlink, but this method has some (mostly minor) drawbacks

Biggest advantage is you can color the TD cell or the whole Table Row TR
(can be done with CSR also, but requires more coding)

• Create a Calculated Column
• Set the datatype to Number

• Paste the Formula

  =IF(ISBLANK([Due Date]),"No Due Date"
  ,"<img src='/_layouts/images/blank.gif' onload=""{"
  &"var R='No Due Date, days left, days past,#f7a,-14,#fab,-7,#fda,0,#cf9'.split(','),"
  &"T=this,D=GetDaysAfterToday(new Date("
  &TEXT([Due Date],"YYYY\,MM\-\1\,D")
  &")),C=3;"
  &"while(Number(R[C+1])<D)C+=2;"
  &"while(T.tagName!='TD')T=T.parentNode;"
  &"T.style.backgroundColor=range[C];"
  &"this.parentNode.innerHTML=Math.abs(D)+R[!(D<0)+1];"
  &"}"">")
  

Notes:

• Full explanation on Why this works (and When not to use it) at: http://www.viewmaster365.com/#/How
• JavaScripts Months start with 0 for january, so you have to substract 1
• the trick image to fire the Javascript is replaced with your text
• Unlike CSR there is no need to include the Due Date in the View
• All this only works in Views, for Forms you have to use CSR

ICC TOP20