Help fix my formula for updating status based on % complete and finish date

AngTrell

I have a formula and I don't know where I've gone wrong. Would love help fixing it! I would like to mark a column as "Complete," "Ongoing,", "Due within 30 days," "Not started," or "Delayed" based on both % column, finish column, and today's date.

This is what I have so far:

=IF([%]@row = 1, "Complete", IF(AND([%]@row < 1, Finish@row > TODAY(), Finish@row <= TODAY(30)), "Due within 30 Days", IF(AND([%]@row > 0, Finish@row > TODAY(), Finish@row <= TODAY(), "On Track", IF(AND([%]@row = 0, "Not Started"))))))

Complete and Due within 30 days work, On Track and Not started do not work. I haven't figured out how to do "Delayed" with a row being 1 but finish being anything past today… Help (and thank you!)

prime_nathaniel

@AngTrell

Your formula is missing parameters, you can see your on track grouping merges your AND criteria with your if then for your fail case to not started

IF(
AND(
[%]@row < 1,
Finish@row > TODAY(),
Finish@row <= TODAY(30)
),
"Due within 30 Days",
IF(
AND(
[%]@row > 0,
Finish@row > TODAY(),
Finish@row <= TODAY(),
"On Track", IF(AND([%]@row = 0, "Not Started"))
)
)
)

You want something more like

IF(
AND(
[%]@row < 1,
Finish@row > TODAY(),
Finish@row <= TODAY(30)
),
"Due within 30 Days",
IF(
AND(
[%]@row > 0,
Finish@row >= TODAY(),
),
"Ongoing",
IF(
AND(
[%]@row >= 0,
Finish@row <= TODAY(),
),
"Delayed",
"Not Started"
)
)
)

You can change your definition of Ongoing and delayed, I had to guess since your original was incomplete. I have ongoing as >0% finish is >= today, Delayed I have as % is > 0 and finish has already passed