Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to change a cell's value if date is in the past and another column's value is not a certain

Helen G.
Helen G.
edited 12/09/19 in Archived 2017 Posts

Hello there, this is my first discussion and I have a feeling this might not be possible, hehe. 


I would like the Status (a dropdown list column) to change to a certain value ("Complete") if the Date (a date column) is in the past and the Type (a dropdown list) is not a certain value ("On Campus").  


Thank you so much!


  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭
    edited 03/03/17

    Hi Helen,


    I tried solving for this with a nested IF formula.



    =IF(Date3 < TODAY(), IF(Location3 = "On Campus", "Complete"), "Future or Off Campus")


    Others here might have additional thoughts or a more elegant solution (which is the beauty of community).


    Let me know if this worked for you.





    Screen Shot 2017-03-03 at 3.32.01 PM.png

  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭

    Oh, I forgot to say... 


    Welcome to the community!

  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭

    Whoops. I took another look at this and I realize I missed a key piece of your question: My formula works if Type = On Campus (you're asking for NOT on campus).


    And, I failed to mention that if you use a formula in the Status column, you couldn't also show the dropdown value in that column. That is, the cell value can contain a value from the dropdown list OR a formula. And if you were to select a value from the dropdown, that would overwrite the formula.


    So ... I will go back to the drawing board here.





  • Taylor F
    Taylor F Employee Admin

    Hello Helen, 


    You will want to use an AND function to test if the date is in the past and of the cell does not equal "On Campus".


    Here is an example of a formula you could use. NOTE - you will need to change the column names and row numbers to reflect your sheet.


    =IF(AND(Date20 < TODAY(), Location20 <> "On Campus"), "Complete")


    Let me know if you have any questions.



  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭

    @Taylor ... Nice! Thanks.


    Helen, let us know if this worked for you. Note that the <> in Taylor's formula is the "Not" part that I was missing.


    Also, note that we've got a collection of help articles to assist with creating formulas. You'll find those in our Help Center here:






  • Thank you, Taylor and Gwyneth, so much! This was very helpful. I understand about the dropdown—I'll think about how to deal with that a different way. The most important thing was this formula! 

    Thanks again,


This discussion has been closed.