Weekly Reporting of Symbol Column
I'm struggling with how to implement a change in how we report project status (red, yellow, green). For each project we have a sheet that holds the reporting data (I've hidden most columns to keep it simple - just project name and health). Health is manually set by the project manager.
This Overall Health field is used in two places - each project's dashboard and an 'Executive Dashboard' that rolls up this sheet from all projects to show high-level RAG of all projects. Two problems we're trying to solve for:
- Taking a weekly snapshot of the overall health column and reporting on that, instead of the potential fluctuations from day-to-day during a reporting week as a project manager updates the health based on the ebb and flow of the project.
- Capturing the duration that the project has been at its current health (in weeks).
The first problem was easy to solve (I think). Rather than the project's dashboard and the Exec dashboard showing the Overall Health, I added a column called "Reported Health" - which is set by a workflow that runs once a week, that aligns with our reporting cycle. So it doesn't matter how many times the Overall Health changes, the dashboards use the Reported Health. There are actually 3 workflows, one for each color change and since you can't update a symbol column from a workflow, I've updated a dropdown column with the health 'text' and have a corresponding symbol column that is controlled by a formula based on the value of this dropdown.
The second problem I haven't cracked yet. What I'd like to do (although the collective experience and wisdom here will likely have a more elegant answer) is have a single workflow that is triggered weekly at a specified date and time that checks if the Overall Health color has changed and then set the Reported Health Text to that value AND set the date (in another new column, called "Reported Health Change Date").
I think this should satisfy the second problem because it only updates the Reported Health and sets the date if the Overall Health has changed in the last week and if not the date holds the value it had when it was last captured and I do a simple date difference calculation to show the duration since that change.
What I haven't been able to figure out is if it's possible to create a workflow that runs once a week at a day and time of my choosing AND based on whether a particular field has changed value.
Any help will be hugely appreciated!