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

Options
✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

If this comment helped you, please help me and help others by using the buttons below if you found it💡Insightfulor❤️Awesome!

Hope you have a great day!!

Ed

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!