3

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 ?

  • Thanks Dan for this solution. However, it does not seem to work for doc libraries on modern sites: DATEVALUE formula is not calculating the value dynamically. Are you experiencing the same? – gtftsmf Jan 8 at 17:04
  • I tried adding this formula but the result said '#NAME?' any ideas :(? – teto89 Apr 17 at 9:20
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review – Gaurravs Apr 17 at 10:01
9

You can create a Calculated Column on the list, with formula (something like):

=([TODAY]-[Created])

OR

=DATEDIF([TODAY], [Created],"d")

Calculated columns use Excel Formulas to calculate the result of the formula (few of the functions are available)

You can read about calculated columns here:
Calculated Field Formulas
Examples of common formulas

UPDATE

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)

  • and if i open the list one day later will all values increment by one ? – user1438082 Jan 27 '14 at 15:15
  • 1
    NOP they won't, please check the update – Arsalan Adam Khatri Jan 27 '14 at 15:23
6

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.

4

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.

=[Created]-TEXT(NOW(),"mm/dd/yyyy")

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.

  • Will it update and increment by 1 every day that passes? – Jurgen Cuschieri Mar 26 '18 at 12:53
3

I would do it with javascript.. then you don't have to update it.

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.
="<script>document.write('TEXT(Created,"YYYY-MM-DD")&");</script>"

  • 1
    thanks for the update :-) forgot that this doesnt htmlencode – Burrhus Jan 28 '14 at 6:32
2

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:

=TEXT(NOW(),"mm/dd/yyyy")-[Created]

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.

1

This should work too.

=CONCATENATE("<p id='days'>"&ROUND(TEXT(NOW(),"mm/dd/yyyy")-Created,0)&"</p>")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.