How to determinate if a project is late according to several dates

Hi,

I hope you will be able to help me. I've tried for many hours, unsuccesfully. I have several dates from which I have to determine whether the implementation of a project is late or not. I don't know if it is the best option but I tried to use the IF function: please see the attached Excel spreadsheet for examples.

Whatever the status of the project (ongoing or finished), I want to know whether it is / was late. I have several columns with dates to consider.

• For ongoing projects, I need to consider today's date (column H) and compare it with the other dates available (column B to E). As you can see, in some instances, not all columns contain a date but I need a formula that will still consider each cell.
• For finished projects, I need to compare the "actual completion date" (column F) with the latest deadline that can be found in either column B to E.

I hope this is clear and that you can help me find a formula or two to quickly determine which projects are on time and which ones are delayed.

Thank you

When is a project considered late? why do you need to consider column B if there are 2nd and 3rd estimated dates that will eventually determine if the project is late?

Hello belinda200, a project will be late when:
For ongoing project:

• today's date > baseline date OR if there is no baseline date
• today's date > max reviewed estimated completion date OR if there is no max reviewed estimated completion date
• today's date > reviewed estimated completion date OR if there is no reviewed estimated completion date
• today's date > estimated completion date

For finished project:

• actual completion date > baseline date OR if there is no baseline date
• actual completion date > max reviewed estimated completion date OR if there is no max reviewed estimated completion date
• actual completion date > reviewed estimated completion date OR if there is no reviewed estimated completion date
• actual completion date > estimated completion date

There are not always 4 dates. For example, we can have an "estimated completion date" and a "reviewed estimated completion date". Or only an "estimated completion date". The baseline date will always be the "biggest" date. It is considered to be the ultimate extended deadline to complete the project and sometimes we will not provide a 3rd date, i.e. a "max reviewed estimated completion date" if a 2nd date, i.e. the "reviewed estimated completion date" has already been determined.

Thank you bebo021999, it works