I need help creating a formula for identifying task status

Here is the formula I'm using...

=IF([End Date]56 > TODAY() + 7, "Future Task", IF(AND([End Date]56 < TODAY() + 7, [End Date]56 > TODAY(), [% Complete]56 < 1), "On Schedule", IF(AND([End Date]56 < TODAY(), [% Complete]56 < 1), "Late", "Complete")))

The formula works to identify Future and Late tasks. How do I update the formula to accurately identify On Schedule tasks based on % complete and End date? In some instances, the formula returns complete for a 0% complete task that should be identified as a Future Task.

Please help.

Find more posts tagged with

Sheets

Formulas

Support

Solution Center

Community Platform

Conditional Formatting

Groups and Group Management

Enhancement Request

Comments

Nic Larsen

Try this:

=IF([% Complete]@row = 1, "Complete", IF([End Date]@row > TODAY() + 7, "Future Task", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Late", "On Schedule")))

PDS

Got it. Thanks.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.