Sharepoint calculated column more date / day calculations
By peter.stilgoe
Avoiding Saturday & Sundays if you were sending out alerts for example:
=IF([Call due day]=”Saturday”,[Call due date]+2,IF([Call due day]=”Sunday”,[Call due date]+2,[Call due date])
Displaying the day as text:
=TEXT(WEEKDAY([Due Date]),”dddd”)
Say you have a date in US format and you want to convert it to UK format:
=TEXT([Your Date Field],”DD-MM-YYYY”)
Sharepoint Calculated Column – Tasks Due This Week
By peter.stilgoe
To show tasks due this week, create the following 2 calculated columns:
Week start (Monday)
=[due date]-(WEEKDAY([due date],2)-1)
Week end (Friday)
=[due date]+(5–WEEKDAY([due date],2))
Create a view where:
Week start is less than or equal to [Today]
AND
Week end is greater than or equal to [Today]
More useful scenarios here:
http://www.myriadtech.com.au/blog/Alana/Lists/Posts/Post.aspx?ID=8
Displaying the week number using a Sharepoint calculated column
By peter.stilgoe
Create & calculated column and add the following formula:
=INT(([Start Date]-DATE(YEAR([Start Date]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Start Date]),1,1)),”d”)))/7)+1
Calculated column – some restrictions
By peter.stilgoe
- You cannot reference a value in a row other than the current row (as opposed to Excel).
- You cannot reference a value in another list or library.
- You cannot reference lookup fields in a formula.
- You cannot reference the ID of a row for a newly inserted row. The ID does not yet exist when the calculation is performed.
Using a calculated column to display month name
By peter.stilgoe
You probably think this is going to be quite long winded & involve many IF statements, its actually pretty simple as I just found out.
Create a ‘Calculated Column’ & for your formula add:
=TEXT([YourDateField],”mmmm”)



September 7th, 2009

![Recommend [pstilgoe]](http://s3.amazonaws.com/arkayne-media/img/badge/logo-recommend-badge-medium.png)