Archive

Archive for September, 2009

Check Null Date and Use Today date in Calculated formula with Nested IF

September 4, 2009 Leave a comment
Scenario is to check a date value if it does not exist then use today date , otherwise use the original date value.
SharePoint does not support TODAY column yet. however there is a workaround and it is not perfect, but it works with workaround
 
Assuming that you are on a task list. You have Due Date column that you want to use. You also have another column as Target Date that you want to check as well.
 
Create a column with name "Today" and keep it as date time
Create another column as Calculated column and i am giving it name here as "Task Status". Write following formula in it.
 
=IF(IF(COUNTA([TargetDate]),[Today],[TargetDate])<[DueDate],"OnTime","Late")
 
Please note that COUNTA is used to count the number of columns that has values in it. so i used it to check if there is a value. it return zero if there is no value.
 
Last step is to delete the Today column that you created above.
 
One draw back is that Today column value does not refresh until the task is updated. You can write a timer job to run once a day to update any column so that calculated column can refresh today value. OR you may just want to create a column of today and update it through timer job every morning.
Categories: Sharepoint 2007