4

I have a column Birthday which contains the date of the user's birthday. I would like to create a calculated column to hold the user's next birthday.

For instance, if I wanted to know the birthday in 2014, this would be the formula:

=DATE(2014;MONTH(Birthday);DAY(Birthday))

But I would really like to know the user's next birthday. Meaning, if the user hasn't had a birthday this year, then the year would be 2014, otherwise, it would be 2015.

More precisely:

If Month(Birthday) > Month(Today): =DATE(2014;MONTH(Birthday);DAY(Birthday))
If Month(Birthday) = Month(Today) && Day(Birthday) >= Day(Today): =DATE(2014;MONTH(Birthday);DAY(Birthday))
Else =DATE(2015;MONTH(Birthday);DAY(Birthday))

However, a formula as simple as the following fails, due to the syntax not being right

=IF([Birthday]>[Birthday], [Birthday], [Birthday])
  • I just updated my answer. Hope that works for you! Let me know how it goes. – Phil Greer May 23 '14 at 21:34
2

It looks like you are using Spanish locale, you should separate with semicolons (apart from the fact that [Cumpleaños] will never be greater than itself):

=IF([Cumpleaños]>[Cumpleaños]; [Cumpleaños]; [Cumpleaños])
2

First, you can't use "Today" in a calculated column in SharePoint, but there is a way around that:

1) Create a temporary column called "Today"
2) Setup your calculated column (with formula referencing the bogus "Today" column - see below)
3) When that's working, delete the temporary "Today" column (you have tricked SP into using Today's date)

Test thoroughly! You may need to re-create and delete your temporary "Today" column several times. For more details, see this blog post.

Second, the formula you need for your calculated column is as follows:

=IF(MONTH(Birthday)>MONTH(Today),DATE(YEAR(Today),MONTH(Birthday),DAY(Birthday)),IF(MONTH(Birthday)<MONTH(Today),DATE(YEAR(Today)+1,MONTH(Birthday),DAY(Birthday)),IF(MONTH(Birthday)=MONTH(Today),IF(DAY(Birthday)>=DAY(Today),DATE(YEAR(Today),MONTH(Birthday),DAY(Birthday)),DATE(YEAR(Today)+1,MONTH(Birthday),DAY(Birthday))))))

Note that the calculated column should be of type "Date and Time".

1

Finally, I created a temporary column "Today" as suggested by Phil Greer, and ended up using the following formula:

=IF(DATE(YEAR(Today);MONTH(Cumpleaños);DAY(Cumpleaños))>Today;DATE(YEAR(Today);MONTH(Cumpleaños);DAY(Cumpleaños));DATE(YEAR(Today)+1;MONTH(Cumpleaños);DAY(Cumpleaños)))

Hope this helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.