Keeping Track of Completion Dates and Updating Status

Art Schneiderheinze

I am relatively new to using Smartsheet, but here is what I'd like to be able to do.

I have a Course Development Tracker sheet that lists courses to be developed in the next term. My team has a 60 day development process, with 5 checkpoints. Each checkpoint should be met by a certain number of days from the due date.

CP#1: DueDate--55

CP#2: DueDate--50

CP#3: DueDate--40

CP#4: DueDate--25

CP#5: DueDate--15

CP#6: DueDate--5

The team prefers having a drop down which they change to reflect the current checkpoint being worked on for the course:

CHECKPOINT

BUS 123 Set to "Checkpoint #3" from the list in the drop-down.

I have a STATUS column with red, yellow, green, to indicating ON TRACK (yellow), NOT ON TRACK (red), NOT STARTED (gray), COMPLETED (green).

RED: User changes the drop down, and if not within the timeline, project is NOT ON TRACK.

YELLOW: User changes the drop down, and if within the timeline, project is ON TRACK.

GRAY: "Not Started" is selected in the drop down.

GREEN: "Project is completed.

Ideally, I would like for Smartsheet to take the TODAY() when the drop-down is changed, look at what is selected in the drop down, calculate the different from the total days available for the project, and return an update to STATUS.

Logically, I can see this, but I am not sure what the best approach would be in Smartsheet. I know how to put a formula in the STATUS column to change the color of the ball based on the value of the drop down, but this doesn't report that the progress is on track or not.

Any thoughts?

 

Mike Wilday

The challenge with this approach is that Smartsheet has no capacity to hard-code Today's date. The Today() function will always update, everytime the sheet is logged into. So your formulas will never be based on a solid date or the date a specific field changed. 

To circumvent this in our course development process we started adding a Actual Start and Actual Finish date. We then base our course Status on those dates. If they have a date column for each phase completion date then you could project the deadline for the next phase based on the date they finished the phase before it. Does that make sense? 

Let me know if you have any questions, or want to discuss this further. 

Art Schneiderheinze

I did think about a column for each checkpoint and place to put a date. The team wanted to avoid have 6 columns so they brainstormed just having one. And i agree using Today() means we wont be able know actual dates. We have start and final date columns and a column that calculates number of days.

I appreciate your comments. And would love to chat more. 

Mike Wilday

You might consider using a third-party tool called Zapier. I am not fluent in using it but it would basically detect changes in the dropdown and set the date it was changed to that setting in Smartsheet. You'd have to have a basic plan and it would be a bit of a cost to implement, but it might work for you. 

Paul Newcome

The simplest way I can think to do it would be to use helper columns for each stage that would calculate the dates accordingly. You can then use a formula to look at today's date and determine if it is before or after a certain date based on your dropdown, etc. I would need to do some testing to be able to give you a more detailed solution.

Mike Wilday

You can now set a start date based on another cell change using automation and automation workflow!

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action?_ga=2.79950226.443393604.1609776795-1074882870.1602114816

Using an automation workflow you should be able to accomplish this now.