Weekly Reporting of Symbol Column

Sing C

Hi,

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:

1. 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.
2. 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!

Thank you.

Sing C

Thanks for the details @Jeff M. I continued to look at it over the weekend and think I have the answer - again, likely not the most elegant approach, but ended up with a solution that requires two workflows. Reason for two workflows is that I believe a workflow can only have one action - so it isn't possible to have a single workflow that records a date AND changes a cell value.

First workflow is to change the date based on 3 conditional paths (the screenshot shows a daily trigger - for testing purposes, it will be a weekly trigger when it gets "deployed to production"):

Each of the conditional paths records the date only if the Overall Health and Reported Health are different but because I couldn't find a way to combine a weekly trigger with checking a column value had changed, I needed to create a conditional path for each color.

The 2nd workflow has the same conditions and executes the color change in 'Reported Health'. Obviously I need to run the date change workflow first to ensure the same starting values in the fields for both workflows, so the date change workflow is scheduled to run an hour before the color change workflow:

I've left this running for a couple of days and it seems to do the job.

Jeff M.

You can change the trigger to the date option, then select custom to set up the daily/weekly check.

I have included examples for the workflow options. Your action would be to "Record a Date" & "Change Text"

I'm not sure if there is a method to check whether the cell contents remained the same. Maybe someone else could offer a solution for that one.

Sing C

Thanks for the details @Jeff M. I continued to look at it over the weekend and think I have the answer - again, likely not the most elegant approach, but ended up with a solution that requires two workflows. Reason for two workflows is that I believe a workflow can only have one action - so it isn't possible to have a single workflow that records a date AND changes a cell value.

First workflow is to change the date based on 3 conditional paths (the screenshot shows a daily trigger - for testing purposes, it will be a weekly trigger when it gets "deployed to production"):

Each of the conditional paths records the date only if the Overall Health and Reported Health are different but because I couldn't find a way to combine a weekly trigger with checking a column value had changed, I needed to create a conditional path for each color.

The 2nd workflow has the same conditions and executes the color change in 'Reported Health'. Obviously I need to run the date change workflow first to ensure the same starting values in the fields for both workflows, so the date change workflow is scheduled to run an hour before the color change workflow:

I've left this running for a couple of days and it seems to do the job.

Jeff M.

That's great! It looks like you were able to cover all angles with your workflows.