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

BrianWong
BrianWong ✭✭
in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Krissia B.
    Krissia B. Moderator
    Answer ✓

    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.gif

    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

    · Share on FacebookShare on Twitter

Answers

  • Devin Lee
    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.

    image.png

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

    · Share on FacebookShare on Twitter
  • BrianWong
    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.


    image.png


    · Share on FacebookShare on Twitter
  • Krissia B.
    Krissia B. Moderator
    Answer ✓

    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.gif

    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

    · Share on FacebookShare on Twitter
  • BrianWong
    BrianWong ✭✭
    https://community.smartsheet.com/discussion/comment/312495#Comment_312495

    Thank you! I will do that!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!