Archive

Archive for May, 2016

SharePoint 2013: Create a Birthday List in the Workplace without Year

The workplace is an unusual space that is required to accommodate a huge variety of personalities while also following laws and doing its best to prevent bias and discrimination. That is an extremely loaded and broad sentence. To narrow the scope, lets bring it down to using SharePoint 2013 in the workplace to develop a dynamically updating list of birthdays for the current month. After all, most everyone loves a good office party! Lets begin.

Create a custom list within SharePoint 2013, if you are unfamiliar with how to create a custom list, this post is not for you and you should step back to learn the basics of list creation and customization. The concepts in this post are not difficult, but we will not be defining how to do the very basics. In this list, you will need a total of 5 different columns:

  • Employee: Single line of text
  • Birthday: Date
  • Birth MonthDay: calculated column
  • Start of Month: calculated column
  • End of Month: calculated column

Employee and Birthday

I defined what each of those columns should be, the nice part is you only need to populate two columns with data: Employee with the Employee’s name and Birthday (mm/dd/yyyy). Entering a persons birthday in this information may not comply with your company’s policy to keep age confidential to prevent age bias or discrimination. To prevent any conflict, simply enter everyone’s birthday with the correct day, but set the year to the current year. So a person name Peter Parker would have a birthday of 12/27/2016. The year doesn’t matter because we will not be calculating age nor do we want to violate a persons right to keep their age confidential.

Birth MonthDay

After you have completed entering the data for the two manual data entry columns of Employee and Birthday, select the column Birth MonthDay. The concept of this column is because the employee has the current birthday of 12/27/2016 and that will obviously not make sense to any viewer. Since a date type column requires a year and shows the year, you can create a custom calculated column to only show the month and date of the the persons birthday. This would turn “12/27/2016” into simply “December 27”. This view makes much more sense to the viewer since it omits the randomly selected year of 2016 for the persons birth year while preserving their age confidentiality.

Select the column Birth MonthDay and ensure the type is calculated. The image below contains the code that you will want to place in the formula box. Note here that you want to make sure the data type returned is set for “single line of text” as this is not a legitimate date column in the mind of SharePoint 2013. The portion of the code TEXT also enforces this, but you must make sure the proper setting is selected or it will give you an error.
SharePoint - birth monthdayNote that in this code, we use “mmmm” to define the intent for the employee’s birthday month to be spelled out fully. There are 4 month specifiers:

  • m = month number (1, 2, 3)
  • mm = month number with two places (01, 02, 03, 10, 11, 12)
  • mmm = month text limited to 3 letters (Jan, Feb, Mar)
  • mmmm = full month text (January, February, March)

Make the selection that best fits what you want for your list to show. Day follows the same rules for the most part: d= number, dd = 01.

Start of Month

The end goal is to show a dynamically updating list of employees birthday of the month, however SharePoint has a hard time doing this natively since it is a dynamic time period, so we have to build a really weird workaround. The next two calculated columns now come into play, starting with the Start of Month column.

Just as was previously done, ensure that the column is set for calculated. There are a few differences we will be doing with this column than the Birth MonthDay column however. We will explicitly declare this column as a date so that SharePoint understands how to understand it when it does its calculations. In the formula, set yours to match that of the image below. You will undoubtedly notice that the order is strange. SharePoint understands dates as DATE(YEAR(variable),MONTH(variable),DAY(variable)). The formular below gathers the YEAR and MONTH from the Birthday field, but the DAY is set explicitly to “1”. Therefore, all dates using this formula will be Birthday’s YEAR, Birthday’s MONTH, but the date will be 1: 12/1/2016. We are doing this because we have to dynamically select the first date of each month for SharePoint to do the proper comparisons/selections later on.
SharePoint - start of monthDifferently than before, ensure the data type returned is Date and Time with Date only selected, as the time is unnecessary.

End of Month

Like the Start of Month, we need to dynamically get the last date of each month. This is done extremely weird however. Every single month has the date of 1, but not every month has the last date as 30, or 31. We also don’t want to exclude anyone who has had a birthday on the 31. The solution to this quandry that not all months have the same number of dates is that we simply go to the next month, go to the first day of it, and go back one day:

Birthday = 1/15/2016. End of Month = 2/1/2016 – 1 WHICH THEN = 1/31/2016

SharePoint knows how to calculate dates, it just needs to know what to do and by subtracting 1 from the first date of the next month, its going back to the last day of the current month. Using this solution, you will always get the last date of each month properly and thus not inadvertently exclude anyone’s birthday. Complete the formula with that from the image below.
SharePoint - end of month

Filtered View

All your columns are now built and all information is now readily available to build the final view. If you look at all the columns together, they should look similar to the below image:
SharePoint - staff birthday listNow you will want to create a new view that you will also set as the “default” view. Name the view whatever, you want. For my purposes, I called the view “Birthday Month”. Set it as the default view as was just mentioned. Check only the Employee and Birth MonthDay for the columns to be shown in this view. Next, jump down to the filter section. Enter the information as depicted in the image below:
SharePoint - filter view settings
This creates an odd range that SharePoint will follow and thus only show the current months birthdays. Basically, the formular doesn’t really consider the birthday at all, just the birthday MONTH and the birthday DAY which we set as the first day of the month and also last day of the month. The date of [Today] is a SharePoint pre-defined function that selects the current date. Putting into english, the above filter states that SharePoint should select every record that has a Start of Month DATE less than or equal to TODAY as well as an End of Month DATE that is greater than or equal to TODAY or:

5/1/2016 5/8/2016 5/31/2016
[Start of Month] [Today] [End of Month]

Explain: [Birthday] is 5/10/2016. [Today] is 5/8/2016. Since for that employee’s list item, they will have a [Start of Month] = 5/1/20xx and a [End of Month] = 5/31/20xx. Therefore, TODAY’s date of 5/8/2016 will meet all the criteria of that employee’s list item entry with their [Start of Month] and [End of Month]. The View however will show just the [Employee] and [Birth MonthDay] column… even though SharePoint is not doing any real calculations with the [Birth MonthDay] column as the [Start of Month] and [End of Month] are dependent on [Birthday].

Now, simply refresh your screen at the default list view and you should see the list of employee names with the birth dates and no year for the current month! Happy Birthday to them.

SharePoint - staff birthday list_final

Update (2/5/2017)

An awesome commenter pointed out a serious flaw. Sharepoint will not show the birthdays each year… only in the CURRENT year of their birthday. Using the above example, the birthdays will only show up in 2016 because I set their birth year as 2016. In order for the birthdays to show up again in 2017, you have to update each birthday to be the same date, but in 2017 instead of 2016. Serious flaw and I apologize if this wasted a lot of peoples’ time. If you have Nintex installed on your Sharepoint, I developed a routine that you run once a year to update all birthdays to the current year by adding 1 to the year. So if you run it multiple times, you will continually increase the persons birth year by one year. I’ll post the Nintex workflow here soon. Special thanks to jeanpaul7 for pointing that out!

Categories: Computers, Technology