Formula to auto pupulate status column based on dates

Sarah_lee123

Hello,

I am trying to get the status column to populate based on date range.

I have a Start and Finish date in each row and want it to do the following

When Start date is < today, say "NOT STARTED"

When Finish date is > today, say "COMPLETE"

This third one is tricky - When Start date is < = today AND Finish date is > = today, then say "IN PROGRESS"

=IF(Start@row<TODAY(), “NOT STARTED”, IF(Finish@row>TODAY(), “COMPLETE”,IF(AND(Start@row>=TODAY(),Finish@row<=TODAY()),“IN PROGRESS”)))

Having the most trouble with the IF(AND...... function

=IF(AND(Start@row<TODAY(),Finish@row>TODAY()),“IN PROGRESS”)

Do I have too many parentheses? Should I be using brackets?

Thanks in advance

Genevieve P.

Hi @Sarah_lee123

IF statements are read in order and only move on to the next IF when the criteria doesn't match.

This means you should be able to simply say "IN PROGRESS" as the final option, because that will only be read if the Start Date is not before Today and the End Date is not in the past (which would be < Today).

As another note, I see your quotes are the wrong type. Smartsheet needs quotes to be straight up and down, like so: " whereas yours are curved... ” vs ". If you type the quotes directly into the cell it will appear in the correct format.

Try:

=IF(Start@row < TODAY(), "NOT STARTED", IF(Finish@row < TODAY(), "COMPLETE", "IN PROGRESS"))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Sarah_lee123

IF statements are read in order and only move on to the next IF when the criteria doesn't match.

This means you should be able to simply say "IN PROGRESS" as the final option, because that will only be read if the Start Date is not before Today and the End Date is not in the past (which would be < Today).

As another note, I see your quotes are the wrong type. Smartsheet needs quotes to be straight up and down, like so: " whereas yours are curved... ” vs ". If you type the quotes directly into the cell it will appear in the correct format.

Try:

=IF(Start@row < TODAY(), "NOT STARTED", IF(Finish@row < TODAY(), "COMPLETE", "IN PROGRESS"))

Let me know if this works for you!

Cheers,

Genevieve

Sarah_lee123

awesome, thanks so much for your help.