Can I add another condition to a formula?

Celia Gust

Hi,

I have a column that I am using a formula to return the Quarter based on a date. The date column doesn't always contain a date though. Sometimes it contains text. Can I add something to the formula to return NA if the content is "Evergreen" and return the Quarter if it's a date?

Here is the formula I am using:

=IF(MONTH([Request RenewalDate]@row)< 4, "Q1", IF(MONTH([RequestRenewal Date]@row)< 7, Q2", IF(MONTH([RequestRenewal Date]@row)< 10, "Q3", "Q4")))

Thanks ahead of time!!

Carson Penticuff

Did you copy the formula out of the column to paste here? [Request Renewal Date] appears with spaces in different places. There is also a quotation mark missing from before Q2.

=IF([Request Renewal Date]@row = "Evergreen", "NA", IF(MONTH([Request Renewal Date]@row) < 4, "Q1", IF(MONTH([Request Renewal Date]@row) < 7, "Q2", IF(MONTH([Request Renewal Date]@row) < 10, "Q3", "Q4"))))

Paul Newcome

Happy to help. 👍️

Check out these two resources:

https://help.smartsheet.com/functions

https://app.smartsheet.com/b/publish?EQBCT=32f8257f5d1948bfa948da170231a0c5&_gl=1*15vbhiy*_ga*MTYwMTgwMzM1Mi4xNjg0NDM4Mzg5*_ga_ZYH7XNXMZK*MTY5MzMzMjY5Ny4yOTguMS4xNjkzMzMyNzM4LjE5LjAuMA..&_ga=2.181698535.1732154367.1693227528-1601803352.1684438389

Paul Newcome

You could wrap the whole thing in an IFERROR.

=IFERROR(original_formula, "N/A")

Carson Penticuff

Did you copy the formula out of the column to paste here? [Request Renewal Date] appears with spaces in different places. There is also a quotation mark missing from before Q2.

=IF([Request Renewal Date]@row = "Evergreen", "NA", IF(MONTH([Request Renewal Date]@row) < 4, "Q1", IF(MONTH([Request Renewal Date]@row) < 7, "Q2", IF(MONTH([Request Renewal Date]@row) < 10, "Q3", "Q4"))))

Paul Newcome

@Carson Penticuff I noticed that too, but I don't think it was copied directly from the sheet. If it was, it would have produced a different error, and I also notice the lack of a space before the "<".

Celia Gust

Thanks, it works now. I thought I had copied it from the sheet, but I must have copied it from my formula cheat sheet because the errors you found were OK on the sheet. That's not the important part though. I was not counting the parentheses correctly, which is what fixed the issue. Thank you both!

Does anyone have a recommendation for where to find basic syntax rules? I'm not finding them and I think that would really help me get more proficient with formulas. Understanding the rules behind parens would especially help. 🤔

Paul Newcome

Happy to help. 👍️

Check out these two resources:

https://help.smartsheet.com/functions

https://app.smartsheet.com/b/publish?EQBCT=32f8257f5d1948bfa948da170231a0c5&_gl=1*15vbhiy*_ga*MTYwMTgwMzM1Mi4xNjg0NDM4Mzg5*_ga_ZYH7XNXMZK*MTY5MzMzMjY5Ny4yOTguMS4xNjkzMzMyNzM4LjE5LjAuMA..&_ga=2.181698535.1732154367.1693227528-1601803352.1684438389

Celia Gust

@Paul Newcome Thank you for these. I will did in!