Featured SharePoint

Fun with SharePoint Calculated Columns

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

Create Calculated Column

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

Calcualted Columns List

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:

Calculated Columns Email

Hope you find this useful!

(Visited 15,479 time, 1 visit today)

4 Comments

Click here to post a comment

  • I’m trying to do something similar but with insurance certificates. I want the status column to automatically tell me if the cert is current or expired. The formula I have so far is – =IF([Expiration Date][Today’s Date],”Current”)) – but the “Today’s Date” is referring to the date I saved the document or data instead of the real time date. How do I fix this?

  • Fun or frustration:
    Why don’t calculated columns (adding together 2 or more numbers from other columns) and with value in the calculated column set as a number, appear in the Y value of chart web part connections? Sharepoint 2010. It seems that they are not being treated as a number, the columns appear in the X values. Straight forward columns with a number in show up ok. Thanks

  • Informative post . I loved the facts ! Does someone know where my business can get access to a blank a form copy to type on ?

About Me

Wendy Neal

Wendy Neal

I am a .NET SharePoint Developer for DMI. I've worked with SharePoint since 2007. I love to share my passion for SharePoint and Office 365 by speaking at various industry and user group events, as well as writing articles for various publications and this blog.   Read More

MCSA Office 365
Top 50 SharePoint Blogs

Buy My Book

Archives

  • 2018 (1)
  • 2017 (1)
  • 2016 (8)
  • 2015 (23)
  • 2014 (20)
  • 2013 (22)
  • 2012 (15)
  • 2011 (13)