Forum Discussion
Help require in Sharepoint for status change based on task completion date and Milestone level.
Hi All,
I need a help in SharePoint, data set is attached. My data is in sharepoint online as list.
- Data set has status column with "open" and "close" status. now when the milestone level is selected to closed, the status will need to auto select as "close". and when the milestone level is selected other than close then status should be "open".
- When the Project Completion Date the date reached to the entered date the Milestone level should get auto closed.
- Aging(Days) column should be stopped when the status is "closed".
currently my aging is calculated as [Project Actual Start date - Today()] which is calculated in power bi.
please help me if above is possible in sharepoint.
1. The first item is just a calculated column with the formula:
=IF([Milestone Level]="11. Closed","Close","Open")2 Your milestone column appears to be a choice column with 11 choices. SharePoint is not able to update this based on another column unless you use a calculated column, but then you wouldn't be able to select the other milestones. You'd need to do this with a flow in Power Automate.
3. A calculated column cannot be stopped from doing the calculation based on another column. Again a flow in Power Automate would be able to handle this. It would run once a day, you would initialize an integer variable, it would get all the items in the list and for each one would check if the status is closed. If it is then do nothing, otherwise in an increment variable action and set it to increment the by 1, then an update item action to update the aging column with the value in the variable. This same flow could also be used to set the value in the milestone column.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
