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)
- 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
- give the column a name “ex: TimeStatus”
- The type of information in this column is: “Calculated (calculation based on other columns)”
- 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”)”
- 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”
- Add the new column to your view of choice to see the automatically calculated status on a task calculated by days left.