Monday, June 2, 2014

SharePoint 2010/2013 - Today as Calculated Column - Used for date age calculation


SharePoint does not allow the Calculated field with function like [Today] or [Me] , And when we required to calculate a age of an issue created from today's date, this will be an issue.  Here is  a simple way to fix this issue

  1. Add a new column to list with Today as Column name and select Date Time as Type.
  2. Select the default value as Today's date and save it 
  3. Create  a new column as Age and select type as calculated field. Use the below formulate to get the date difference. 
  4.         =IF([Created]="","NA",Today-[Created]) 
  5. Select the data type returned from this formula as Number and set number of decimals to 0 and save it.
  6. Now delete the Today columns that is created from step 1. ( if you don't delete this , answer will be based on this column which will be empty and you'll get a -ve value).
  7. Now your list display difference as the age in days.
Here , you can use any other date column that you have created. This formula will give you the result as difference between today and created date. EX: Created Date = 5/1 and Today is 6/2, then Age = 32.  And for custom columns if no date is present, this will display NA.

PS: The calculated fields will not auto refresh every day. For auto refresh, it has to go through custom code or timer job.

Happy Browsing!


No comments:

Post a Comment