Running a workflow to change the status in a column when a date is reached

mballe

Hello! I am having trouble finding the correct way to build a workflow for this column. I need a workflow that will trigger the dots to change to the correct colors when specific dates are reached.

John_Foster

Using the formulas I included the colors should show correctly for all months in the past. I replaced the check to today's date that was in the formula's just to check I had the colours showing correctly as time passed.

In the example image it was checking the Enrollment Status assuming the date was 10th December 2023.

The same image using todays date would like like the image below.

If this has helped please mark the answer and/or flag the post with any of the other options as appropriate to allow others to see the correct answer if they search this post in the future.

Thanks,

John

John_Foster

Hi @mballe,

What you are trying is possible, can you provide the name of the column that you want to change the colors and the rules, someone can then help you with the formula.

John

mballe

Hi @John_Foster

I don't have any other columns that are changing. What I need is for the column dot in this column to turn green during certain month and to automatically change to that color when the first of that month date is reached.

Does that make sense?

John_Foster

Hi @mballe,

Would the certain month change for different cells? If so how will you determine this. When the 1st of the month is reached would you want the cell to show yellow or red? And under what circumstance would the other color show?

Do you have a picture you could upload with some sample data that we could use to help?

What you are after is possible, but any additional help would be good to assist with the formula?

Thanks,

John

mballe

@John_Foster

So, the green should show during the enrollment month, the yellow should show for the month prior, and the red should show for the rest of the time.

There is one other column labeled "enrollment month" that tells you which month is their enrollment month.

So, for the ones that enroll in January, on 12/01, I need the dot to turn yellow, on 01/01 to turn green, and then 02/01 to turn back to red.

Is that the information you were looking for?

John_Foster

Hi @mballe,

I have looked at the image and you explanation. The best way I can find to do this without complex formulas is to create 3 fields on the Sheet Summary (more detail about sheet summary can be found using the link at the end of this post).

The first field is called ThisMonth with a formula to calculate the first day of the current month as below.

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

The second field is called NextMonth with a formula to calculate the first day of the next month as below.

=IF(MONTH(TODAY()) = 12, DATE(YEAR(TODAY()) + 1, 1, 1), DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1))

The third field is called FollowingMonth with a formula to calculate the first day of the following month as below.

=IF(MONTH(TODAY()) = 12, DATE(YEAR(TODAY()) + 1, 2, 1), IF(MONTH(TODAY()) = 11, DATE(YEAR(TODAY()) + 1, 1, 1), DATE(YEAR(TODAY()), MONTH(TODAY()) + 2, 1)))

In the field Enrollment Status I have then used the following formula to set the colors.

=IF([First Enrollment Date]@row < CurrentMonth#, "Red", IF([First Enrollment Date]@row < NextMonth#, "Green", IF([First Enrollment Date]@row < FollowingMonth#, "Yellow", "Red")))

The screenshot below is based on the current date being 10th December 2023. Are the colors showing as you would expect?

In addition, the formula below should take the month from the date entered using a formula to save someone having to select it manually and was used in the Enrollment Month field in the screenshot above..

=IF(MONTH([First Enrollment Date]@row) = 1, "January", IF(MONTH([First Enrollment Date]@row) = 2, "February", IF(MONTH([First Enrollment Date]@row) = 3, "March", IF(MONTH([First Enrollment Date]@row) = 4, "April", IF(MONTH([First Enrollment Date]@row) = 5, "May", IF(MONTH([First Enrollment Date]@row) = 6, "June", IF(MONTH([First Enrollment Date]@row) = 7, "July", IF(MONTH([First Enrollment Date]@row) = 8, "August", IF(MONTH([First Enrollment Date]@row) = 9, "September", IF(MONTH([First Enrollment Date]@row) = 10, "October", IF(MONTH([First Enrollment Date]@row) = 11, "November", "December")))))))))))

https://help.smartsheet.com/articles/2476786-define-your-work-with-sheet-summary?source=apphelpicon

Hope this helps!

John

mballe

@John_Foster

I think I understand now.

In your example, the colors aren't quite right. All months that are a past date should be red. It would turn yellow the month before and green the month of.

I think I can use a similar workflow to do that now, though. Thank you!

John_Foster

Using the formulas I included the colors should show correctly for all months in the past. I replaced the check to today's date that was in the formula's just to check I had the colours showing correctly as time passed.

In the example image it was checking the Enrollment Status assuming the date was 10th December 2023.

The same image using todays date would like like the image below.

If this has helped please mark the answer and/or flag the post with any of the other options as appropriate to allow others to see the correct answer if they search this post in the future.

Thanks,

John