Workflow to Update Date to TODAY() until another cell = 100%

BrianWong

I have 2 cells in a column:

1. Which displays a % from 1-100
2. Which should show a date

Is it possible to use a workflow or other automation to daily (at 12:01am or any other time) update the date field to TODAY(), but to stop updating it once the % field = 100%?

Thanks in advance!

Krissia B.

Hello @BrianWong

Thank you for posting additional details. At this time, there isn't a direct to do what you are trying to accomplish to record a date when you are not using a DATE column, but it is a great idea! Please submit an enhancement request to have this considered for future development in Smartsheet.

In the meantime, an alternate solution would be to create a helper column (date column) that you can hide then convert that into a text value under your "Project End Date." See my sample below on how it would look like!

Formula used for the Project End Date. I used an IFERROR function so that it doesnt give an error message if the % Complete is not 100%. I used something like "Not Completed" or you can leave it blank, its up to you.

=IFERROR(IF(MONTH([Proj1 - End Date]@row) = 1, "January", IF(MONTH([Proj1 - End Date]@row) = 2, "February", IF(MONTH([Proj1 - End Date]@row) = 3, "March", IF(MONTH([Proj1 - End Date]@row) = 4, "April", IF(MONTH([Proj1 - End Date]@row) = 5, "May", IF(MONTH([Proj1 - End Date]@row) = 6, "June", IF(MONTH([Proj1 - End Date]@row) = 7, "July", IF(MONTH([Proj1 - End Date]@row) = 8, "August", IF(MONTH([Proj1 - End Date]@row) = 9, "September", IF(MONTH([Proj1 - End Date]@row) = 10, "October", IF(MONTH([Proj1 - End Date]@row) = 11, "November", IF(MONTH([Proj1 - End Date]@row) = 12, "December")))))))))))) + " " + DAY([Proj1 - End Date]@row) + ", " + YEAR([Proj1 - End Date]@row), "Not Completed")

Then i used the formula for the hidden helper column (date column) as the reference.

=IF([Proj1]5 = 1, TODAY())

Hope this helps!

Cheers~

Krissia

Devin Lee

Set the formula to =Today() in the fields and then make an automation to record a date when it reaches 100%. Just an FYI the record a date doesn't work well with dependencies. More info can be found in the link below.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action

BrianWong

https://community.smartsheet.com/discussion/comment/312113#Comment_312113

Thanks - my issue is a bit different but possibly because I've set up my sheet a bit weird. There is no Date column - the sheet is being used to show different milestones pulling in from other projects like below. Because of this, each project/external sheet is a column in order to do the other calculations (Workstream1's formula is a count of complete child tasks/total children). This works for all cells except the cell "Project End Date" which I wanted to set to TODAY() until the "% Complete"=100%. But because the column is Proj1 and is not a date column (it has dates, numbers, text), I am not sure the workflow you suggest will work. Is it even possible to achieve what I'm trying to do with this setup? If not, I can continue manually putting in Project End Date since the rest of the sheet works.

Krissia B.

Hello @BrianWong

Thank you for posting additional details. At this time, there isn't a direct to do what you are trying to accomplish to record a date when you are not using a DATE column, but it is a great idea! Please submit an enhancement request to have this considered for future development in Smartsheet.

In the meantime, an alternate solution would be to create a helper column (date column) that you can hide then convert that into a text value under your "Project End Date." See my sample below on how it would look like!

Formula used for the Project End Date. I used an IFERROR function so that it doesnt give an error message if the % Complete is not 100%. I used something like "Not Completed" or you can leave it blank, its up to you.

=IFERROR(IF(MONTH([Proj1 - End Date]@row) = 1, "January", IF(MONTH([Proj1 - End Date]@row) = 2, "February", IF(MONTH([Proj1 - End Date]@row) = 3, "March", IF(MONTH([Proj1 - End Date]@row) = 4, "April", IF(MONTH([Proj1 - End Date]@row) = 5, "May", IF(MONTH([Proj1 - End Date]@row) = 6, "June", IF(MONTH([Proj1 - End Date]@row) = 7, "July", IF(MONTH([Proj1 - End Date]@row) = 8, "August", IF(MONTH([Proj1 - End Date]@row) = 9, "September", IF(MONTH([Proj1 - End Date]@row) = 10, "October", IF(MONTH([Proj1 - End Date]@row) = 11, "November", IF(MONTH([Proj1 - End Date]@row) = 12, "December")))))))))))) + " " + DAY([Proj1 - End Date]@row) + ", " + YEAR([Proj1 - End Date]@row), "Not Completed")

Then i used the formula for the hidden helper column (date column) as the reference.

=IF([Proj1]5 = 1, TODAY())

Hope this helps!

Cheers~

Krissia

BrianWong

https://community.smartsheet.com/discussion/comment/312495#Comment_312495

Thank you! I will do that!