# DateTime calculated Column using Formula

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

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

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

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!