Calculated Columns

Case 1

Used Columns: COL_TXT1, COL_NO1, COL_NO2
Calculated Column Formula:
=IF(COL_TXT1=””;IF((COL_NO1+19)<=(COL_NO2+7);COL_NO2+7;COL_NO1+19);””)

Explanation:
If text column is blank show nothing, else based on number columns show a calculated value
Example: 
COL_NO1+19 =<COL_NO2+7 then COL_NO2+7 else COL_NO1+19

LINK
SharePoint 2010: N/A
SharePoint 2013: Yes
SharePoint 2016: N/A
SharePoint Online: Yes

Case 2

Used Columns: COL_CHO1, COL_NO1, COL_DAT1, COL_DAT2
Calculated Column Formula:
=IF(COL_CHO1=”PM DUE”;COL_DAT1+COL_NO2;COL_DAT2)

Explanation:
If the choice column equal specific value, show the primary date column plus n days., else show the second date column
Example: 

LINK
SharePoint 2010: N/A
SharePoint 2013: Yes
SharePoint 2016: N/A
SharePoint Online: N/A

Case 3

Used Columns: COL_DAT1, NDAYS ( Numeric )
Calculated Column Formula:
=IF(WEEKDAY((COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1))=1;(COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1)-2;(COL_DAT1+NDAYS+(IF((TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))>0;2*(TRUNC((NDAYS-(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0)))))))/5))+2;IF(NDAYS>(IF(NDAYS>(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));(IF(WEEKDAY(COL_DAT1)=2;5;IF(WEEKDAY(COL_DAT1)=3;4;IF(WEEKDAY(COL_DAT1)=4;3;IF(WEEKDAY(COL_DAT1)=5;2;IF(WEEKDAY(COL_DAT1)=6;1;0))))));NDAYS));2;0)))-1))

Explanation:
A standard request for leave form
Example: 

LINK
SharePoint 2010: N/A
SharePoint 2013: N/A
SharePoint 2016: N/A
SharePoint Online: Yes

Case 4

Used Columns: COL_DAT1
Calculated Column Formula:
=CONCATENATE(1+INT(MONTH(Date)/4),”/”,TEXT(Date,”yy”))

Explanation:
From a date type column, get the Quarter and the year in format Q/YY
Example: 

LINK
SharePoint 2010: N/A
SharePoint 2013: Yes
SharePoint 2016: N/A
SharePoint Online: N/A

Case 5

Used Columns: COL_TXT1, COL_CHO1
Calculated Column Formula:
=[COL_TXT1]&”/”&[COL_CHO1]

Explanation:
Concatenate a single line column with a choice column with an ” / ” in the middle Example:

LINK
SharePoint 2010: N/A
SharePoint 2013: N/A
SharePoint 2016: N/A
SharePoint Online: Yes

Case 6

Used Columns: COL_DAT1, COL_DAT2
Calculated Column Formula:
=(HOUR(COL_DAT1)+HOUR(COL_DAT2)+INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60))&”:”&(IF(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)<10;”0″&(MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60));MINUTE(COL_DAT1)+MINUTE(COL_DAT2)-60*INT((MINUTE(COL_DAT1)+MINUTE(COL_DAT2))/60)))

Explanation:
Sum of time values for two DateTime columns
Example: 

LINK
SharePoint 2010: N/A
SharePoint 2013: N/A
SharePoint 2016: N/A
SharePoint Online:Yes

 

In examples I used following columns

COL_TXT1 _ Single line text column
COL_TXT2 _ Single line text column
COL_CHO1 _ Choice column
COL_CHO2 _ Choice column
COL_NO1 _ Numerical Column with 0 decimals
COL_NO2 _ Numerical Column with 0 decimals​
COL_DAT1 _ DateTime Column – only Date
COL_DAT2 _ DateTime Column – only Date
COL_DTT1 _ DateTime Column – Date and Time
COL_DTT2 _ DateTime Column – Date and Time
COL_YN1 _ Yes/No Column
COL_YN2 _ Yes/No Column

This entry was posted in CalculatedColumn, Sharepoint 2010, Sharepoint 2013, Sharepoint Online. Bookmark the permalink.