Using [Today] in a Calculated Formula (Birthday Lists)

For the last time, this is not MOSS!

Ok, we all know that SharePoint loves to complain about how you can't use [Today] in a calculated column…

"TODAY - Returns the serial number of the current date. The serial

number is the date-time code used for date and time

calculations.

Note  You can only use the TODAY function as a default

value; you cannot use it in a calculated column.")

Well, I can't take credit for this one but I found this thread in the forums by a gentleman named Pete Blair... I also was watching a thread at SharePointU so I thought it was worthy of a Blog post to answer once and for all! ;)

It is possible to use "Today" in a function as a reference to today’s date

(despite what SharePoint tells you).  There is a very simple work around that

doesn't involve very much effort or complexity.

1. First, create a new column in your list with the column name of "Today".  
   Click "OK".  (It doesn't matter what type of column or data it is, this is
   just a place holder and will be removed later).
   MK Note: Create column called Today... I also created a column called DOB (date field) where I will enter the birth date.
2. Next, create a column with the data where you would like to use "Today" as a
   reference to today’s date.  The column type should be "Calculated".  In the
   formula field, create your formula using "Today" as if it held the column date/time.
   SharePoint will calculate the formula based on the assumption that you will
   be using "Today" as a reference to the new column you just created.
   MK Note: I created a column called MonthCheck with the formula shown below:
    =IF(MONTH([Today])=MONTH([DOB]),"Birthday","NotBirthday")
3. Next, edit the new column named "Today" that you created in the first step.  
   On the very bottom of the page, click "Delete", to delete the column.  In
   your formula, SharePoint will keep the reference to "Today" but it will
   change from referencing your column, to a reference to the current date.
4. MK Note: Now you can create your new view in the list to display “This Month's Birthdays“.  Just apply a filter to Show only when the following is true: MonthCheck is equal to Birthday
   I've also created a BirthDay Column (Capitalized the “D“ to note the difference) as well that is a calculated column: =DATE(YEAR(Today),MONTH(DOB),DAY(DOB))
   You can then create a view to display Today's Birthdays by filtering to Show only when the following is true: BirthDay is equal to [Today]

This can be used in any of the Date and Time functions, but I haven't tested

it beyond that.

Notes:

If you want to edit the formula (CheckMonth) containing "Today" SharePoint will not let

you, and will give you the same error about not using Today or Me in a

function.  To get passed this problem you have to temporarily create another

Today column (and then delete it again).

The same functionality will also work with [Me].

Ted Tang has a great post on using “< MONTH />” by making modifications within FrontPage:How to ONLY Display "This Month" Items in a List