Automated Drop down or Formula?

JDSpec13

Hi all, I was wondering if there is a formula or drop down function that can auto populate words or drop downs? As seen in my conditional formatting for "Days Left Till Expiration it conveys whether if it is red, Yellow, Or Green based on the amount of days passed or upcoming till a Term end date. I'm wondering if I can auto populate something similar to my drop down list I'm attempting to signify whether it is Past Due (Red), Upcoming (Yellow), Or Expiring Greater than or Equal to 01/01/2025 (Green).

Thank you in advance!

Paul Newcome

You would use a nested IF.

https://help.smartsheet.com/function/if

JDSpec13

@Paul Newcome Hi Paul, I believe I am doing something wrong with the code as it is reading #Unparseable Could you possibly advise?

I've tried =IF([Term End Date]@row]1="Red", "Past Due", "On Time")

&

=IF([Term End Date]@row[2 < TODAY(), "Red", IF([2 > TODAY(), "Yellow", IF(2 = TODAY(), "Green")))

Paul Newcome

What is your column name?

JDSpec13

@Paul Newcome The column name I'd like to extract the color from would be Column "Days Left Till Expiration" as I have a =TODAY() - [Term End Date]@row function with color based formatting but the raw data in date stems from "Term End Date", & I would like to convey in column "Past Due or Current" if it is Past Due, Upcoming, Or Expiring Greater than or Equal to 01/01/2025 in words

JDSpec13

@Paul Newcome I got it so thank you for the help!

If there is anyone looking at this forum in the future I scrapped the drop down or symbol and used "Expired, Upcoming, or "Next Year"- the formula I used was

=IF([Term End Date]@row <DATE(2024, 1, 25), "Expired", IF(AND([Term End Date]@row >= DATE(2024, 1, 25), [Term End Date]@row <= DATE(2025, 1, 25)), "Upcoming", IF([Term End Date]@row >DATE(2025, 1, 1), "Next Year", "")))

"[Term End Date]@row" is achieved by manually clicking the "Term End Date" Column Row 1 (01/02/19)

*in my case*