1-I am trying to reference another to provide results of contract expiration date -

2- I would like to display in a dashboard the percent of total contracts that has expidered, will expire in 1year, 6month, 90 days 30 days, 14 days and 7 days

3- finally if there was an extension, to input that date in a new column that will indicate that, but will now give the new values

=IF({contract end date}, >TODAY(365), "CURRENT-Expires in more than a year", IF(AND({contract end date} < TODAY(31), {contract end date} >= TODAY()), "CURRENT - Expires in less than 1 month", IF(AND({contract end date} < TODAY(365), {contract end date} > TODAY()), "CURRENT-Expires within 1 year", IF(AND({Contract Range 1} < TODAY(), {contract end date} > TODAY(-365)), "Expired less a year ago", IF(AND({contract end date} < TODAY(-366), {contract end date} > TODAY(-730)), "Expired less than 2 years ago", IF(AND({contract end date} < TODAY(-731), {contract end date} > TODAY(-1825)), "Expired between 2-5 years ago", IF({contract end date} < TODAY(-1826), "Expired more than 5 years ago")))))))


  • Mike TV
    Mike TV ✭✭✭✭✭✭


    At the very start of your formula you have this:

    =IF({contract end date}, >TODAY(365), 

    You need to remove the comma after your cross-sheet reference so it reads like this:

    =IF({contract end date} >TODAY(365), 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!