For a Sharepoint 2007 list

I would like to have a calculated column that calculates a 2 way status based on a calculation of days between 2 dates. (ex: project time status)

Prerequisites:

  • We have a Sharepoint 2007 list
  • We have at least “design” level permission (Can create lists and document libraries and edit pages in the Web site)  to the site where the list is located
  • we have 1 column called  [Scheduled GMT] (default 

At present I was not able to find a useable and workable solution to populate the today column automatically with todays date unless the entry is updated. So for now I update the [today] column manually in a separate hidden column user for my calculation.

We now go to -> Settings ->  List Settings -> create column

  1. give the column a name “ex: TimeStatus”
  2. The type of information in this column is: “Calculated (calculation based on other columns)”
  3. in the Formula field insert:”=IF(((DATEDIF(Today,[Scheduled GMT],”d”))-INT(DATEDIF(Today,[Scheduled GMT],”d”)/7)*2-IF((WEEKDAY([Scheduled GMT])-WEEKDAY(Today))>0,2,0)+1<6),”Imminent”,”On Time”)”
  4. Where [Scheduled GMT] is the column with the GMT Time and [today] is my hidden column with the manual today’s date input for the calculation. <6 means that above 6 days left the task is “on Time” else if less its “Imminent”
  5. Add the new column to your view of choice to see the automatically calculated status on a task calculated by days left.

 

 

%d bloggers like this: