Fun with SharePoint Calculated Columns

I recently had a client requirement to send out an automated company-wide birthday greeting email whenever it was someone’s birthday. I decided to use SharePoint Designer to create a workflow that sends an email, and then run it on a timer job that runs nightly. This timer job would pull all employees that have a birthday that day from a SharePoint list, then fire off the workflow for each birthday found.

No problem, that sort of thing I’ve done before, be it in other workflow software or in Sharepoint. They did have one requirement that stumped me for a little bit: They wanted 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.

I explored ways to do this by setting variables in the workflow itself based on the day portion of the Date of Birth field. Looked too complicated if even possible. I didn’t want to write a Visual Studio workflow to handle this one requirement. So what did I do?

Calculated Columns to the Rescue

I decided to create a few calculated columns to handle the day suffix, and well as displaying the day and the name of the birthday month. I created the following columns:

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. You will just grab the day from the date using the DAY function.

=DAY([Date of Birth])

Birthday Day Suffix

This is the tricky one. I ended up using 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, you can’t do traditional if-then-else statements. However, you 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 your list’s List Settings page and add a new column. Then choose Calculated for the column type and fill out the Formula field.

After creating my calculated columns, my list looks like this:

Then when creating the body of the email in SharePoint Designer, I just used the Birthday Month Name, Birthday Day, and Birthday Day Suffix fields to create my text, like so:

Hope you find this useful!