Formula Help Please!

Jim Schaffhausen

Hi Community-

I have a column that I want to say what phase a project is in based upon the criteria below

4 Possible Phases – “Strategy & Intake”, “Brief Finalization”, “Detailed Concepts”, “Production to Market”

#1 “Strategy & Intake” – Milestone “BRIEF COMPLETED & APPROVED” is not checked and is not in the next 21 days or more.

#2 “Brief Finalization” – Milestone “BRIEF COMPLETED & APPROVED” is not checked and is in the next 20 days

#3 “Detailed Concepts” – Milestone “BRIEF COMPLETED & APPROVED” is checked

#4 “Production to Market” – Milestone “APPROVED DETAILED CONCEPTS” is checked

Any help using the @row would be much appreciated!

 

Frank Falco

You need to consider the order of evaluation. In this case, this is the order I would use:

#4 - If Approved Detailed Concepts is checked, then nothing more to check

#3 - If both Brief Completed & Approved is checked then nothing more to check

Check that Milestone is a Date otherwise, cannot evaluate days ahead

#2 - Brief Completed & Approved is not checked & in next 20 days <=Today(20)

#1 - Brief Completed & Approved is not checked & 21 days  or more >TODAY(20)

Use @row in place of row numbers so speeds up calc and reordering.

Assuming Brief Completed, Approved, Approved Detailed Concepts are all individual checkbox columns, and Milestone is a date column, try this code in the Phases column.

=IF([Approved Detailed Concepts]@row=1,"Production to Market",

IF(AND([Brief Completed]@row=1,Approved@row=1),"Detailed Concepts",

IF(AND(ISDATE(Milestone@row), [Brief Completed]@row=0,Approved@row=0),

IF(Milestone@row<=TODAY(20), "Brief Finalization", "Strategy & Intake"))))

It is a little ugly but should work.

Paul Newcome

Are all of the milestones in the same column, or are they column headers with dates/checks going across a row?

Jim Schaffhausen

Hi Paul-

They are going across a row.

Paul Newcome

Give this a try...

 

=IF([Approved Detailed Concepts]@row = 1, "Production to Market", IF([Brief Completed & Approved]@row = 1, "Detailed Concepts", IF([Brief Completed & Approved (date)] >= TODAY(-20), "Brief Finalization", "Strategy & Intake")))