Tips about Calculated Columns in SharePoint

SharePoint Foundation’s formulas for calculated fields are based on Microsoft Excel’s functions and syntax.

How do you apply it to SharePoint and use it for your business needs?

The Scenario:

Employee can use SharePoint to check their Paid Annual Leave (number of days taken, number of days left and so on) and then they can arrange their vacation.

Logic of Paid Annual Leave:

Logic of Paid Annual

 

SharePoint Solution:

Paid Annual Leave List:

Employee Name, Employee ID, Department, Type, Work Start Date, Date on Board, Start Date, Annual Entitlement, Actual Annual Entitlement, Annual Leave Taken, Annual Leave Remaining, Notes.

The “Today” Column is the same as the constant [Today] in SharePoint which is automatically written by Boostsolutions’ Workflow Boost. (For more details see: http://www.boostsolutions.com/workflow-boost.html)

Annual Entitlement: means the number of days off an employee is entitled to; this is based on the number of years they have worked and the Paid Annual Leave for the current year.

Actual Annual Entitlement: means the number of days off an employee is entitled to; this is based on the number of days worked in the company and the number of days off an employee is entitled to.

 

Calculated Column:

Annual Entitlement, Actual Annual Entitlement, Start Date, Days left

 

Formula:

Annual Entitlement=IF(Type =”Company”, IF(Today-Date on Board>365,4+ROUNDDOWN(DATEDIF(Date on Board, Today,”d”)/365,0),0),IF(Today- Work Start Date>7300,15,IF(Today-Work Start Date>3650,10,5)))

 

Actual Annual Entitlement =IF(Type=”Company”, IF(DATEDIF(Date on Board, Today,”d”)<365,0,ROUNDDOWN(DATEDIF(Date on Board,Today,”d”)/365,0)+4),IF(DATEDIF(Date on Board,Today,”d”)>365,Annual Entitlement ,ROUNDDOWN((DATEDIF(Date on Board, Start Date ,”d”)/365*Annual Entitlement),0)))

 

Start Date =IF(Type =”Company”, DATE(YEAR(Today),MONTH(Date on Board),Day(Date on Board)),DATE(YEAR(Today),12,31))

 

Annual Leave Remaining = ROUND (Actual Annual Entitlement, 0)-ROUND (Annual Leave Taken,1)

 

Function description:

Function ROUNDOWN: To round down the number of dates between Date on Board and Today.

Function DATEIF returns the number of days between the Date on Board and the Start Date of Paid Annual Leave.

 

For more details please refer to

http://msdn.microsoft.com/en-us/library/bb862071.aspx