Help with IF function combining multiple columns and greater than today dates

RPlaud

Hello I am trying to write a formula that returns one of the following "Not Yet Started, "In Progress," or "Complete" based on dates being in the past or future and multiple columns.

So I would like the Study Status Prod column to say "Not Yet Started" if the date in the Anticipated Study Start Date column is before today and the Study Status Prod column to say "In Progress" if the date in the Initiation of Study Date column is before today and the Study Status Prod Column to say "Complete" if the Final Report (Target Date) column has a date after today.

Is there a way to write this formula?

Thank you in advance!

Kelly Moore

Hello @RPlaud

The formula handbook is a great reference.

I made some assumptions on your process flow. Will the formula below work for you?

=IF(AND(ISDATE([Final Report (Target Date)]@row), [Final Report (Target Date)]@row <= TODAY()), "Complete", IF(AND(ISDATE([Initiation of Study Date]@row), [Initiation of Study Date]@row <= TODAY()), "In Progress", IF(AND(ISDATE([Anticipated Study Start Date]@row), [Anticipated Study Start Date]@row <= TODAY()), "Not Yet Started")))

Kelly

edapel

I am not the best with formulas, but Smartsheet has helped with the Formula Handbook Template. Check it out, may help with your question.

https://app.smartsheet.com/b/publish?EQBCT=32f8257f5d1948bfa948da170231a0c5&_gl=1*1dg9kb7*_ga*MTY4NjI2MzQxMC4xNjk2MDE4MDMy*_ga_ZYH7XNXMZK*MTcwNDczNzkzOC41Ny4xLjE3MDQ3Mzg0NzIuMzIuMC4w&_ga=2.114617038.896392365.1704716205-1686263410.1696018032

Kelly Moore

Hello @RPlaud

The formula handbook is a great reference.

I made some assumptions on your process flow. Will the formula below work for you?

=IF(AND(ISDATE([Final Report (Target Date)]@row), [Final Report (Target Date)]@row <= TODAY()), "Complete", IF(AND(ISDATE([Initiation of Study Date]@row), [Initiation of Study Date]@row <= TODAY()), "In Progress", IF(AND(ISDATE([Anticipated Study Start Date]@row), [Anticipated Study Start Date]@row <= TODAY()), "Not Yet Started")))

Kelly

RPlaud

I cannot thank you enough! This worked perfectly!!!