Checking Complete based off various criteria
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
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
Private Start Date
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.
Help Article Resources
Check out the Formula Handbook template!