Help with Nested IF and OR Formula to Auto-Fill Status Based on Multiple Conditions

Sertqa123

Sure! Here's a draft for a new discussion post for smartsheet.com under the Formulas and Functions category:

Title: Help with Nested IF and OR Formula to Auto-Fill Status Based on Multiple Conditions

Category: Formulas and Functions

Body:

Hi Smartsheet Community,

I’m trying to build a formula that auto-fills the Status column based on values in the Start Date, End Date, and Completion % columns. Here’s what I want the logic to do:

• If Completion % is 100%, return "Completed"
• If today’s date is between Start and End Date, and Completion % is less than 100%, return "In Progress"
• If today is past the End Date and Completion % is less than 100%, return "Overdue"
• If the Start Date is in the future, return "Not Started"

I’ve been trying something like this, but it’s not working as expected:

=IF([Completion %]@row = 1, "Completed", IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row, [Completion %]@row < 1), "In Progress", IF(AND(TODAY() > [End Date]@row, [Completion %]@row < 1), "Overdue", IF(TODAY() < [Start Date]@row, "Not Started"))))

Can someone help me check if this formula is correct, or suggest a better way to write it?

Thanks in advance!

Let me know if you'd like to customize this post with different logic or column names!

Georgie

Hi @Sertqa123,

I see that you used AI to write this post. Was the AI tool able to answer the question for you, or do you still need help with the formula?

Thanks,

Georgie