Checking Complete based off various criteria

Peggy Parchert

I am hoping someone can help me. I want to make the Complete column a column formula column in my Project Tracking sheet so that I don’t have to babysit and mark every line (we have hundreds) for when a package is completed but I can’t get my formula to work. 

Here is what I have for my current formula:

=IF([Go-Live Date]@row > TODAY(), 0, IF(OR(COUNT([Event Start Date]@row:[Online Start Date]@row) = 0, [Event Start Date]@row < TODAY(), [Private Start Date]@row < TODAY(), [Online Start Date]@row < TODAY()), 1, 0))

It isn’t marking line items as Complete if only one type has dates (Package 12) and it’s marking a line item as Complete if it has no dates (Package 12S) - see below.

I have column formula columns for:

Planned: =IF(Complete@row = 1, 0, 1)

Tentative: =IF(Confirmed@row = 0, 1, 0)

Retired: =IF([Go-Live Date]@row > TODAY(), 0, IF(OR([Event End Date]@row > TODAY(), [Private End Date]@row > TODAY(), [Online End Date]@row > TODAY()), 0, 1))

Note: we use 12/31/9999 as End dates until we have an actual End date

————————————————

Criteria:

1. The package has to be Confirmed before it can be marked Complete.

2. If there is a Go-Live date, the Complete column is checked based off that date

3. If there is no Go-Live date, then it needs it to check the Complete column based off:

Event Start Date

Or

Private Start Date

Or

Online Start Date

4. Exceptions - these are handled differently. They won’t have a Go-Live date but will have an Event Start/End date once Confirmed. The Event Start/End date columns are usually blank for these until the actual date they start. All “exceptions” are checked (Exception column).

5. It is possible (for a short timeline) to have a new line item with no dates in any of the date columns and be Confirmed. These shouldn’t be marked Complete (we haven’t started them yet). 

When a new line is added to the Project Tracking sheet, it is automatically check-marked as Planned & Tentative. We manually update the Confirmed column and all the Start/End date columns. 

Any help would be appreciated.

Thanks -Peggy

Kimberly Loveless

Would this formula work? It is similar to the one you indicated above but with a few tweaks

=IF(AND(Exception@row=0, Confirmed@row=1,OR([Event Start Date]@row < TODAY(), [Private Start Date]@row < TODAY(), [Online Start Date]@row < TODAY(),[Go-Live Date]@row < TODAY())),1,0)

Peggy Parchert

@Kimberly Loveless

Thank you - that fixed my problem with exceptions. However, it marked Package 8 as Completed and it shouldn't be marked as Completed yet. All start dates and go-live date are in the future. And Package 12 should be marked Complete and isn't. Suggestions?

Kimberly Loveless

So Package 12 is because confirmed isn't checked and the only thing I can think of is that when it is running the calculation for some reason the year is reading as 1999 instead of 9999 for the event date and private end date for some reason but I am not positive on that.

Peggy Parchert

@Kimberly Loveless - I wanted to thank you again for the assistance with this. I ended up going a different direction - I created an automation rule to update a cell.

Peggy