4

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 ?

3
  • 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, 2019 at 17:04
  • I tried adding this formula but the result said '#NAME?' any ideas :(?
    – testo89
    Apr 17, 2019 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, 2019 at 10:01

6 Answers 6

8

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)

4
  • and if i open the list one day later will all values increment by one ? Jan 27, 2014 at 15:15
  • 1
    NOP they won't, please check the update Jan 27, 2014 at 15:23
  • 1
    Cannot use [TODAY] in SP 2019+.
    – vapcguy
    Feb 7, 2023 at 21:04
  • DATEDIF() gives #NUM! errors and thus is useless.
    – vapcguy
    Feb 8, 2023 at 21:40
8

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.

2
  • 1
    Will it update and increment by 1 every day that passes? Mar 26, 2018 at 12:53
  • 1
    @JurgenCuschieri Of course it would. It would take the Created Date and subtract it from NOW(), which changes by the day. Everyday it would grow as NOW() is farther away from when it was created.
    – vapcguy
    Feb 8, 2023 at 21:29
5

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.

1
  • Should explain more fully what should be in the JSLink, and specifically what formula.
    – vapcguy
    Feb 7, 2023 at 21:07
5

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
  • TEXT(NOW(),"mm/dd/yyyy")-Created works well, though needs rounding. I used: =ROUND(TEXT(NOW(),"mm/dd/yyyy")-Created, 2). DATEDIF() didn't give me any results, I believe I also got #NUM! errors
    – vapcguy
    Feb 8, 2023 at 21:33
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>"

2
  • 1
    thanks for the update :-) forgot that this doesnt htmlencode
    – Burrhus
    Jan 28, 2014 at 6:32
  • That just writes the date it was created, not the difference between now and that time.
    – vapcguy
    Feb 8, 2023 at 21:30
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 and acknowledge you have read our privacy policy.

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