How to calculate a date and maintain the record

[em_amdahl]

I have a choice field in a sharepoint list. The choices include Acknowledged & Resolved. I'd like to have a date record registered when we change the choice to either of those. 

I have been using this formula: 

=IF([Issue Status]="Acknowledged",TODAY(),"")

But when I change the status to resolved it removed the date from the field. How can I keep the date from going away when I change the choice field. 

[#2]

@em_amdahl your formula should be =IF(OR([Issue Status]="Acknowledged", [Issue Status]="Resolved"), TODAY(),"")

As a calculated column doesn't update dynamically when the date changes it will maintain the date when the choice column was changed to Acknowledged or Resolved.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#3]

Is there a way to restrict it from pulling a date when they select "resolved"?

I have to pull a date field for when it is resolved and when it is acknowledged.

[#4]

@em_amdahl sorry, I don't understand what you mean by pulling a date.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

[#5]

@em_amdahl 

I don't think it is possible using the SharePoint calculated column formula because every time you edit/update the list item (any column value) or calculated column formula itself, the calculated column formula will execute and overwrite the previously saved date value (TODAY() - when status was changed) in the column.

I think you should use the Power automate flow for this:

1. Create a date column in your SharePoint list
2. Create a new flow which will run on item creation and update (When item is created or modified trigger)
3. Add trigger condition based on date column so that the flow will run only when the date column is empty
4. Inside your flow, add "Condition" action to check if Issue Status is equal to Acknowledged
5. Inside "If yes" block of condition action, use "update item" action to update date column to current/today's date using utcNow()

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.