Home > Sharepoint 2007 > Check Null Date and Use Today date in Calculated formula with Nested IF

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

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.
Advertisements
Categories: Sharepoint 2007
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: