Project Status using IF Condition

[ragav_in]

Dear Members,

I am trying to find out the status of a project from a list of projects based on certain conditions, and I would like to have an IF formula for the same. The data I have for each Project is Due Date & 3 Deliverables Name & their Status (Approved, Not Approved). My condition are:

1. If the Due Date is Less than Today()+30 days, and All 3 Deliverables Status are Approved, the Project is On-Track
2. If the Due Date is Less than Today()+30 days, and If any one of the Deliverable is not of the Status Approved, then the Project is Delayed
3. If the Due Date is More than Today()+30 days, then the Project is On-Track
4. Today() is being considered as 14-Apr-22 (current date)

A	B	C	D	E	F
Project Name	Due Date	Deliverable 1	Deliverable 2	Deliverable 3	Status
Project 1	30-Apr-22	Approved	Approved	Approved	On Track
Project 2	30-Apr-22	Approved	Approved	Not Approved	Delayed
Project 3	31-May-22	Not Approved	Not Approved	Not Approved	On Track

I tried the below formula (under the Status Column F), but I am getting an error and I am unable to find out the reason. The expected output is in the above table

=IF(B2<TODAY()+30,IF(OR(C2<>"Approved",D2<>"Approved",E2<>"Approved", IF(B2>TODAY()+30,IF(NOT(C2<>"Approved",D2<>"Approved",E2<>"Approved"),"Delayed", "On Track")))

Can any one provide some assistance here to get this IF condition working which can populate the status of the projects based on the 4 Columns as shown above? Your time and help in this regard is deeply appreciated.

Thanks in advance for your help and guidance.

Thanks,
ragav_in

 

[Fluff]

How about

=IF(B2<TODAY()+30,IF(COUNTIF(C2:E2,"Approved")=3,"Ontrack","Delayed"),"Ontrack")

 

[ragav_in]

Dear Fluff, thanks much for the correct formula. I understand that you have used to Logic of counting the "Approved" in 3 columns, if the date is less than the expected date to return ontrack, else delayed. This is what I had expected, and thanks for the timely response.

However, may I request you to let me know if possible what is the "miss" in the formula that I have written, just in order to understand where I went wrong. Also can this be done using an AND, OR and IF statement? Just wanted to do a retrospect and hence asking this; please respond in case if you have time.

Once again, thanks much for the help with the formula, and your help is deeply appreciated.

Thanks,
ragav_in

 

[Alex Blakenburg]

See if this helps, this is how yours should look:

=IF(B2<TODAY()+30,
              IF(AND(C2="Approved",D2="Approved",E2="Approved"),
                       "On-Track",
                        "Delayed"),
               "On-Track")

If < Today() + 30 then if all approved On-Track if not then Delayed (ie one of the 3 must not be approved)
Otherwise it must be >= Today() + 30, in which case its On-Track

 

[ragav_in]

Hi ALex, thanks for the solution, and it gives me the expected result. Am a big fan of this forum. Thanks, ragav_in

 

[Alex Blakenburg]

Appreciate the feedback. Glad we could help.