Formula to auto pupulate status column based on dates

Options

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

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Options

    awesome, thanks so much for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!