Sharepoint calculation

Question

I am trying to create a column on a share point list that will show the number of days it is open from another column.

The column is a choice column - open or closed - once the choice is closed then the number in the new column should revert to 0 - if its still open then the new column will show how many days its opened.

Answer 1

Hi Antonio,

I'm writing this reply to follow up on this thread, may I know if you have checked my July 1 reply? If my reply is helpful, please sign into your account first, and then send feedback on it. This will help other members who have the same concern.

Sincerely,

George | Microsoft Community Moderator

Answer 2

Hi Antonio,

Sorry for my late reply.

Here is a solution to meet your needs.

You need to create a Date column, a Calculated column, and a Power Automate flow.

1. The Power Automate flow adds today's date to the Date column only when the list item's Choice field is changed to "Open". Updating the list item's other fields won't update the Date field.
2. The Date column is used to store the date when the item's Choice field is changed to "Open".
3. The Calculated column is used to count the days between two dates(the recorded date and today) when the item's choice field is "Open".

Here is the test result.

Here are the steps.

1. Create a Date column
2. Create a Power Automate flow as shown in the screenshots below

For the "When an item is created or modified" trigger, add a trigger condition via the three dots> Settings. Remember to replace "choice" with your Choice column name in the following expression.

@equals(triggerOutputs()?['body/choice/Value'],'Open')

In the "Get changes for an item or a file" action, add "Trigger Window Start Token" and "Trigger Window End Token"

In the Condition, add two conditions with the OR function

The first condition, add "Has Column Changed: choice" from "Get changes for an item or a file", is equal to, input the expression "true".

The second condition, add two sub-conditions with the And function

choice Value, is equal to, Open

Date column, is equal to, null

In the Date column field, input the expression "formatDateTime(utcNow(),'yyyy-MM-dd')". If your date format is not like this, you need to change the 'yyyy-MM-dd' part.

3. Create a Calculated column

Click "Add column", select "See all column types", click Next. Choose the column type "Calculated", input the following formula, and choose "Number" as the data type returned from this formula.

Remember to replace it with your column name.

=IF([choice]="Closed",0,IF(OR(ISBLANK([choice]),ISBLANK([Date1])),"",DATEDIF([Date1],TODAY(),"D")))

![Image](https://learn-attachment.microsoft.com/api/attachments/be85358c-1dbe-4973-9a89-4a5e78511abc?platform=QnA"4">

Answer 3

Hi Antonio,

Thanks for posting in the community. We are happy to help you.

Per your description, the built-in features in SharePoint can't meet your needs. You will need to build a Power Automate flow to accomplish this. For the detailed steps, we need time to test and will update the thread shortly.

Sincerely,

George | Microsoft Community Moderator