I have a list. When an item is created it is automatically assigned the created date. I would like to add a “days open” column. "Days open" being the number of days from the created date to today’s date. Is there an easy way to achieve this ?
You can create a Calculated Column on the list, with formula (something like):
Calculated columns use Excel Formulas to calculate the result of the formula (few of the functions are available)
As suggested everywhere, you can use Powershell Script/Console Application and schedule it to run daily to keep
[Today] up-to-date, please check below:
Dynamically Updating a SharePoint Calculated Column Containing a "Today" Reference
How to keep SharePoint calculated columns up-to-date
USE POWERSHELL TO UPDATE A [TODAY] COLUMN DAILY USING A SCHEDULED TASK
PS: If this seems dirty way, you can write a Custom Timer Job to calculate the days for you and set it to a Field (non Calculated Column) and schedule the Timer Job to run daily (12 AM)
You can make use of JS link for the displaying the "days open" value in the column.
To do that, create a column "Days Open" in the list. Using JSLink to calculate the value using the desired formula.
Refer link here to understand the basic of JSLink. You have to replace the required logic of calculating the value before displaying on list view.
This way we don't have to call update everyday. Calculation will happen whenever the user tries to view the list view.
I just tried to use [Today] in calculated formula in SharePoint 2013 but got the below error.
calculated columns cannot contain volatile functions like today and me.
I tried the below formula and it worked perfectly fine to calculate the days difference between created date and today.
The NOW() provides the same functionality as [Today] and gives today's Date & Time and we can set any custom format with it. You can try multiple combinations depends on your needs to define a custom date format. See this.
Hope that helps someone.
Something like this: create a calculated field with numeric data type (to avoid htmlencoding)
then you can add something like this.. Of course with the correct script instead of this quick test.
This formula works, but if there is less than 24 hours between the two dates it will evaluate as less then one for those two days:
So if you use a number format in your calculated field with zero decimal places, it will be off my one day if there is less than 24 hours between the two times.
I also tried these formulas, SharePoint saves the columns, but then the result is calculated as "#NUM!":
=DATEDIF(NOW(), [Created], "d")
=DATEDIF(TEXT(NOW(),"mm/dd/yyyy"), [Created], "d")
I found that this is the most reliable calculation:
=DATEVALUE(TEXT(NOW(),"mm/dd/yyyy")) - DATEVALUE(TEXT([Created],"mm/dd/yyyy"))
DATEVALUE converts the date to the number of days since 1/1/1900, just like dates in Excel work. This formula avoids any time-of-day problems and does not require a daily workflow to update a column in the list.