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

Helen G.

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

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.

 

Cheers!

Gwyneth

 

Gwyneth C

Oh, I forgot to say... 

 

Welcome to the community!

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

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

@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:

 

https://help.smartsheet.com/topics/formulas-and-functions

 

Cheers!

Gwyneth

Helen G.

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,

Helen