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])
Improve this question
1
  • I just updated my answer. Hope that works for you! Let me know how it goes. Commented May 23, 2014 at 21:34

3 Answers 3

Reset to default
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])
Improve this answer
0
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".

Improve this answer
2
  • Thank you! +1 for the workaround of using this temporary column. However, the formula failed because an error in the syntax. Commented May 27, 2014 at 15:53
  • Debunked in 2008 that this trick is useless: blog.pathtosharepoint.com/2008/08/14/… Commented Jun 23, 2015 at 12:38
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!

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.