Calculated columns: Using [Today] in a Sharepoint calculated column

Published on

When using [Today] in a formula / calculated column in SharePoint you get the following error: “Calculated columns cannot contain volatile functions like Today and Me” For SharePoint 2010 / SharePoint 2013 / SharePoint Online you can use the Today() function eg. =TODAY()+365   =IF(AND([Date Ordered]<Today()-5, OR([Purchase Status]=”SOLD”,[Purchase Status]=”NOT SOLD”,[Purchase Status]=”ITEM NEVER DELIVERED”,[Purchase Status]=”NO STOCK … Continue reading Calculated columns: Using [Today] in a Sharepoint calculated column

Sharepoint 2010 Content Type Hub – New calculated column not propogating to children

Published on

Had a weird problem where I was updating a master content type, adding a calculated column to it which I wanted to propogate down to all content types inheriting from the master but no matter what the new calculated column never appeared. It turned out that the logic & syntax in my calculated column didnt … Continue reading Sharepoint 2010 Content Type Hub – New calculated column not propogating to children

Sharepoint calculated column more date / day calculations

Published on

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

Published on

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

Using a calculated column to display month name

Published on

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”)

Sharepoint Common Date Formulas For Calculated Columns

Published on

Get Week of the year: =DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]))+0.5-WEEKDAY(DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])),2)+1 First day of the week for a given date:=[Start Date]-WEEKDAY([Start Date])+1 Last day of the week for a given date: =[End Date]+7-WEEKDAY([End Date]) First day of the month for a given date: =DATEVALUE(”1/”&MONTH([Start Date])&”/”&YEAR([Start Date])) Last day of the month for a given … Continue reading Sharepoint Common Date Formulas For Calculated Columns