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 make sense although it did accept it upon creation. Once I rectified the forumla in the calculated column so it was correct the column propogated to all child content types as it should do.

Hopefully this will solve someone sometime if they experience similar!

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

More From pstilgoe

• Calculated column – some restrictions
• Sharepoint Calculated Column – Tasks Due This Week
• Displaying the week number using a Sharepoint calculated column

Ask pstilgoe To Recommend Your Posts

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

More From pstilgoe

• Sharepoint calculated column more date / day calculations
• Displaying the week number using a Sharepoint calculated column
• Calculated column – some restrictions

Ask pstilgoe To Recommend Your Posts

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

More From pstilgoe

• Sharepoint calculated column more date / day calculations
• Sharepoint Calculated Column – Tasks Due This Week
• Calculated column – some restrictions

Ask pstilgoe To Recommend Your Posts

- 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.

More From pstilgoe

• Sharepoint calculated column more date / day calculations
• Sharepoint Calculated Column – Tasks Due This Week
• Displaying the week number using a Sharepoint calculated column

Ask pstilgoe To Recommend Your Posts

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

More From pstilgoe

• Sharepoint calculated column more date / day calculations
• Displaying the week number using a Sharepoint calculated column
• Calculated column – some restrictions

Ask pstilgoe To Recommend Your Posts

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 year (does not handle Feb 29). Result is in date format:
=DATEVALUE (CHOOSE(MONTH([End Date]),31,28,31,30,31,30,31,31,30,31,30,31) &”/” & MONTH([End Date])&”/”&YEAR([End Date]))
Day Name of the week : e.g Monday, Mon
=TEXT(WEEKDAY([Start Date]), “dddd”)
=TEXT(WEEKDAY([Start Date]), “ddd”)

The name of the month for a given date – numbered for sorting – e.g. 01. January:
=CHOOSE(MONTH([Date Created]),”01. January”, “02. February”, “03. March”, “04. April”, “05. May” , “06. June” , “07. July” , “08. August” , “09. September” , “10. October” , “11. November” , “12. December”)

Get Hours difference between two Date-Time :
=IF(NOT(ISBLANK([End Time])),([End Time]-[Start Time])*24,0)

Date Difference in days – Hours – Min format : e.g 4days 5hours 10min :
=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today) DAY(Today) <=MONTH(Created),DAY(Today) >=DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today) “&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)

More From pstilgoe

• Sharepoint calculated column more date / day calculations
• Calculated column – some restrictions
• Sharepoint Calculated Column – Tasks Due This Week

Ask pstilgoe To Recommend Your Posts