Sharepoint, Calculated column, IF function and date
I am trying to add a calculated column.
I have a date column containing the date a meeting is scheduled. From this column I need a code which can return if the meeting is scheduled in Q1, Q2, Q3 or Q4. I have a static code looking like this:
'=IF(Date<40269;"Q1";"Q2-4")' (40269 is the 1. April 2010 and Date=date-column)
But I need somekind of dynamic code which can calculate the same thing next year as well, without someone having to change the number(40269). I need something like this:
'=IF(Date<01-01-&year(today);"Q1";IF(Date<01-04-&year(today);"Q2";IF(Date<01-07-&year(today);"Q3";"Q4")))'
But Sharepoint will not accept a date written like this 01-01-2010, it needs to be a number eg. 40269. The above code will only work correct for the present year, but thats all-right, since I will only use data from the present year.
Can anyone help me?
Answer
Ahhhh... It was easier than I had anticipated. I used this function:
=IF(MONTH(Date)<4;"Q1";IF(MONTH(Date)<7;"Q2";IF(MONTH(Date)<10;"Q3";"Q4")))
Date is a column containing the date of the meetings. month() returns the number of the month. E.g., march=3.