Use of SharePoint Calculated Columns

Why Calculated columns important?

Assume we need to send out an automated company-wide birthday greeting email whenever it was an employee's birthday. The option that will come to our mind suddenly :

1.  Use SharePoint Designer to create a workflow that sends an email.
2.  Run a timer job every night.  This timer job would pull all employees that have a birthday that day from a SharePoint list, then trigger the workflow for each birthday found.

Then we may have one requirement that, in the body of the email for it to say “Happy Birthday to [employee name] on October 2nd” for example.  But if it’s the 1st, then there should be an “st” on the end of the birthday day.  Or “th” if it’s the 4th, and so on.

This is where the Calculated columns come into play.

Calculated Columns to the Rescue

We can create a few calculated columns to handle the day suffix, and well as displaying the day and the name of the birthday month, as below.

Birthday Month Name

It’s pretty easy to derive the name of the month from a particular date field using a simple formula.  The second parameter passed to the TEXT function is what tells it how to format the month, in this case it is formatting as the full month name, for example, October.

=TEXT([Date of Birth],”mmmm”)

Birthday Day

Very similar to the first example.  We will just grab the day from the date using the DAY function.

=DAY([Date of Birth])

Birthday Day Suffix

This is the tricky one.  We can use a combination of nested IF functions and the OR function in order to achieve the desired result.  Since a formula for SharePoint calculated columns need to be written in one line of code, we can’t do traditional if-then-else statements.  However, we can use nested IF statements, but if you have a lot of “else” clauses, it can get very messy.

=IF(OR([Birthday Day]=”11″,[Birthday Day]=”12″,[Birthday Day]=”13″),”th”,(IF(RIGHT([Birthday Day])=”1″,”st”,(IF(RIGHT([Birthday Day])=”2″,”nd”,(IF(RIGHT([Birthday Day])=”3″,”rd”,”th”)))))))

To create a calculated column, simply go into the list’s List Settings page and add a new column.  Then choose Calculated for the column type and fill out the Formula field.