Archive for Sharepoint 2007

Sharepoint 2007: list calculated column calculates a 2 way status based on days left between 2 dates

Andreas Schmidt / June 2, 2011 / No comments

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.

 

 

Category: Calculated Columns, Sharepoint, Sharepoint 2007 / Tags: , , , ,

Sharepoint 2007: list calculated column calculate days between 2 dates

Andreas Schmidt / June 2, 2011 / No comments

For a Sharepoint 2007 list

I would like to have a calculated column that calculates the days between 2 dates.

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 

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

  1. give the column a name “ex: Days left”
  2. The type of information in this column is: “Calculated (calculation based on other columns)”
  3. in the Formula field insert:”=IF(ISERROR(DATEDIF([Scheduled GMT],[start_date],”d”)),DATEDIF([start_date],[Scheduled GMT],”d”),-DATEDIF([Scheduled GMT],[start_date],”d”))”

    Where [Scheduled GMT] is the column with the GMT Time and [start_date] is the column with a start date for the calculation.

  4. Add the new column to your view of choice to see the automatically calculated days between the 2 dates.

 

Category: Calculated Columns, Sharepoint, Sharepoint 2007 / Tags: , ,

Sharepoint 2007: Calculated Column with 5 hours offset for GMT to EST conversion

Andreas Schmidt / June 2, 2011 / No comments

For a Sharepoint 2007 list

I would like to have a calculated column that calculates the GMT -5 =EST hours for a task list.

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 

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

  1. give the column a name “ex: Scheduled EST (GMT-5)”
  2. The type of information in this column is: “Calculated (calculation based on other columns)”
  3. in the Formula field insert:
    “=[Scheduled GMT]-TIME(5,0,0)”
    Where [Scheduled GMT] is the column with the GMT Time and (5,0,0) are the hours,minutes,seconds you would like to subtract.
  4. Add the new column to your view of choice to see the automatically calculated EST time based on your GMT entry.
Category: Calculated Columns, Sharepoint, Sharepoint 2007 / Tags: , ,
 
Close
loading...