Forum Discussion
Change choice column based on Date
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.
SteveKnutson 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.
- 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.- AcidrsCopper Contributor
SteveKnutson 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!