Thursday, July 3, 2008

Creating a view for birthday

Well, I struggled with this for an hour or so until I finally found a solution...

Scenario:
our customer has a list of users with a date field with their Birthday.
He wishes to see the upcoming birthdays for users in the upcoming week.

Problem:
The birthdate field holds the actual birthday of the user (i.e. July 24 1980), but he wants to see it as July 24 2008... July 24 2009... and so on.

Well, since we cannot use the [Today] field in calculated column (all tricks wont work - the field won't update!), I have conjured this solution that only asks for a yearly update to the column:

First, we have to create a calculated column that results in the current year birthdate
(from July 24 1980 to July 24 2008)

1 - create a date column to hold real birthday (named: BDay) or type date.
2 - create a calculate column to hold this year's BDay (named: YBDay).
3 - set the output type of the calculated value to "Date and Time"
4 - use this formula to generate its value:
=DATE(2008,MONTH(BDay),DAY(BDay))


Now, the rest is easy:
You create a view and set filter to 2 conditions:
where [this year birthday] >= [Today]
And
where [this year birthday] <= [Today] + 7


Now, you see all birthdays for the upcoming week!

Just remember - next year you will have to update the formula of the calculated field.

Hope this helps you - sure did saved me :)

9 comments:

Sigitas said...

[quote]Just remember - next year you will have to update the formula of the calculated field.[/quote]

So why not just tweak formula a little bit:
=DATE(YEAR([Today]),MONTH(BDay),DAY(BDay))

Shai Petel said...

Hi Sigitas,

Thanks for you Q,

I actually explained why: "since we cannot use the [Today] field in calculated column".

If it had supported that we would not have any problems creating a yes/no field named "Has a BDay this month".

Thanks, Shai.

Matt said...

Hi Shai,

I'm new to Sharepoint administration and this was one of the first things that we wanted to implement.

I thought I put in everything correctly but I get the following error message when trying to put in the second (calculated column).

"The formula contains reference(s) to field(s)."

I must be doing something wrong but I am not sure.

Any ideas?

Thanks.

Scott said...

Brilliant Shai

Just what I was after!

Works a treat. Shame about having to update the year, but that's no biggie

Dentxinho said...

Hello Shai,
you wrote

"I actually explained why: "since we cannot use the [Today] field in calculated column".

If it had supported that we would not have any problems creating a yes/no field named "Has a BDay this month"."

Actually you can do this. Create a column with the name "Today", and then create this calculated field returning yes/no, and after delete the "Today" column.

Justin said...

Has the issue with the [TODAY] function being in a calculated column and failing to update been addressed/corrected in SharePoint 2010?

Shai Petel said...

which issue? in view or in calculated column?

From what I seen so far it is still the same...

Scott said...

Shai,

How would you do this if you want to see birthdays for the current month? I.E. I want to see all birthdays that fall in February.

Shai Petel said...

Hi Scott, wow this is an old post.

I posted something more recently here: http://kwizcom.blogspot.ca/search?q=data+view+plus which have a much more advanced birthday view, this example has a property "numOfWeeksToShow" which sets how many weeks ahead to search from today, if you set it to 4 you should get about a month ahead.

Let me know what you think.