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”)
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!
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 ?
Hello trevor! my friend saw a blank Form UCC1 example using this