Calculated Column Issue - Days Open

HelpDesk
1

Hello,

I am trying to get a calculated column to tell me how many days open a ticket is and then I am running a Schedule that auto-closes some tickets after a certain amount of days. Here is the formula for the calculated column -

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

The column seems to work correctly for a day or two and then the column shows 0 for some tickets and #NAME on others. Any ideas?

Thanks!

2

I don’t think Now() or Today() are supported in calculated columns. You have to handle that via views.

3

Hello Chase!

Calculated column

Unfortunately, I can't explain why it works in the beginning and then stops. You could check the following approach.

  1. Create a Today column (format its output as date and time):
=TEXT(NOW(),"mm/dd/yyyy")

image
image637×643 19.2 KB

2. And in the DaysOpen column (I formatted it as a number), use this formula:

=DATEDIF(Created,Today,"D")

image
image605×660 19.9 KB

Perhaps, it will help. If not, I would advise you to contact Microsoft support since it is pure SharePoint functionality. I would like only to draw your attention that the calculated column is required only for displaying the number of days. Scheduler tasks don't need it (see below).

Scheduler task

Regardless of the calculated column, you can create a task that will use functions in its condition to define whether it is time to close tickets.

AddDays([Ticket.Created], 10) - adds 10 days to the date from the Created column.
Date(AddDays([Ticket.Created], 10)) - takes the date part without time from the previous action.

image
image833×742 23.8 KB

4

The calculated column would require to be edited to capture the latest now or today. When you enter a formula using these types it will only take a snap shot at that moment.
It seems like a flaw within calculated column, but i can see why Microsoft would have build it that way. They want you to use views.

5

Indeed, the Today column is not refreshed until an item is updated.

1 Like