IF Statement based on Date

Jorge Silva

Greetings Community

I'm trying to build a formula that gives me different results based on the date in one column, but one of my weaker spots is always the nested IFs. To give you context of the expected result:

I have a column that tracks the different dates of Contract renewals and I want to add a new one that gives me the Status of the renewal timeline and my logic goes something like this:

IF [Renewal Date] greater than Today = "Expired"

IF [Renewal Date] between today and 60 days in the past = "Upcoming"

IF [Renewal Date] less than Today minus 60 days = "On Time"

IF [Renewal Date] is blank = "No Renewal"

I know probably is very simple but I always get confused in how to order the statements, commas and parenthesis.

Can you please help me??

Jorge S

Christian G.

I use a notepad to write nested ifs clearly, then I remove the formating to place it in a cell.

1rst, lets convert the pseudo code to smartsheet syntaxt:

IF([Renewal Date]@row>Today(),"Expired", else...)

IF([Renewal Date]@row >=Today(-60),"Upcoming", else...)

IF([Renewal Date]@row <Today(-60),"On Time", else...)

IF(isblank([Renewal Date]@row),"No Renewal", "")

Now we need to put them all in one line.

=IF([Renewal Date]@row>Today(),"Expired",IF([Renewal Date]@row >=Today(-60),"Upcoming",IF([Renewal Date]@row <Today(-60),"On Time",IF(isblank([Renewal Date]@row),"No Renewal",""))))

Christian G.

I use a notepad to write nested ifs clearly, then I remove the formating to place it in a cell.

1rst, lets convert the pseudo code to smartsheet syntaxt:

IF([Renewal Date]@row>Today(),"Expired", else...)

IF([Renewal Date]@row >=Today(-60),"Upcoming", else...)

IF([Renewal Date]@row <Today(-60),"On Time", else...)

IF(isblank([Renewal Date]@row),"No Renewal", "")

Now we need to put them all in one line.

=IF([Renewal Date]@row>Today(),"Expired",IF([Renewal Date]@row >=Today(-60),"Upcoming",IF([Renewal Date]@row <Today(-60),"On Time",IF(isblank([Renewal Date]@row),"No Renewal",""))))

Jorge Silva

Thank you very much! that worked.

And thanks for the tip about writing stuff down on a notepad first. Will do it next time.