DateTime calculated Column using Formula

Question

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])

Answer 1

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])

Answer 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".

Answer 3

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!