Looking for a formula to return one of two text options based on a date

Hello,

What formula would I use for this scenario?

If (column name) is before (date), return "text option 1" and if (column name) is after (date), return "text option 2".

Thank you!

Answers

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Amber Bailey UNM ,

    Does this work for you?

    =IF([column name]@row<=DATE, "TEXT 1", "TEXT 2")

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • Amber Bailey UNM
    edited 11/15/21

    Hi @hollyconradsmith,

    Thanks for the formula, but it's giving me an Invalid Operation.


    This is what I put in

    =IF([Current Hire Date*]@row <= 12/01/16, "6 months", "3 months")


    I need to be able to update the year each year so there is a five year lookback. If someone has been here for more than five years we are supposed to give them 6 months notice if their contract is being non-renewed and we are to give them 3 months notice if they've been here less than three years. The column "Current Hire Date*" is set up as a Date column and the date format is MM/DD/YY. Not sure what I'm getting wrong. Please note that the asterisk is actually part of the column header name, so, I'm not sure if that may cause an issue with the formula.

  • hollyconradsmith
    hollyconradsmith ✭✭✭✭

    Hi @Amber Bailey UNM ,

    Try using TODAY() in place of the hard date.

    Holly Conrad Smith

    Director of Technology & Innovation 💡 at Streamline

    CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

  • @hollyconradsmith , thank you but I ended up building a filter to help identify these. The TODAY() won't work because I'm using a fixed date and this could be run at any time.

    Thanks for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!