SOLVED

Change choice column based on Date

Copper Contributor

Hello,

I am looking to see if the value of a choice column in SharePoint can be set based on the Date of another column, or by the color of another column. 

I have an "End Date" column that i have already formatted it by color coding it based on if it is Past todays date, Within 30 days of todays date or longer. 

I have another column called "Status" which is a choice column. I was looking to see if i could set the values of the column fields based on either the color of my "End Date" column or if the date had passed, within 30 days or longer than 30 days.

 

3 Replies
You can't do this in SharePoint, but it can be done using a PowerAutomate 'Scheduled' Flow.
Schedule the Flow to run once a day and use Get Items action with a filter based on the date, to find the items to update.
best response confirmed by Acidrs (Copper Contributor)
Solution

@Steve Knutson Thanks for your response. 

I ended up changing the "Status" column from a choice field to a calculated column and used the following formula to have it auto set the field.

=IF([End Date]>TODAY()+60,"Active",IF([End Date]>TODAY()+30,"Expiring Soon","Expired"))

This also allows the status column to dynamically  update when the different date requirements are met. 

That is a great approach. Thanks for sharing the solution. Well done!
1 best response

Accepted Solutions
best response confirmed by Acidrs (Copper Contributor)
Solution

@Steve Knutson Thanks for your response. 

I ended up changing the "Status" column from a choice field to a calculated column and used the following formula to have it auto set the field.

=IF([End Date]>TODAY()+60,"Active",IF([End Date]>TODAY()+30,"Expiring Soon","Expired"))

This also allows the status column to dynamically  update when the different date requirements are met. 

View solution in original post