Formula Recommendations

BO'Neil

Formula Recommendations

I’m constructing a post project review dashboard and would like to highlight projects that fall within the planned project timeline estimate and the actual completion date.

I have a project In-Take sheet that has the following columns that I believe I can use in a formula to provide me the visual indicator to be displayed in reports and dashboards.

I need to compare project start date to the project completion date and then determine if the result falls within a project complexity timeframe.

I could use some help constructing a formula that would achieve my end goal.  

Current columns

• Project Status (Report should only include Complete)
• Project Complexity  (High, Med, Low)
• Project Active Date
• Project Complete Date

Potential additional columns

• Scope of Work estimate in Months (not currently in my intake sheet but I’m thinking of adding it so I can build in more flexibility by project vs. a flat High = X months, Med = Y, & Low =Z)
• Planned vs. Actual Status (RYG ball vs. setting the background color of Project Complexity column)
• Others?  (Based on TBD design recommendations)

The below pseudo code seems to look like it might work if I can get a formula constructed to match.

If Project Status@row is Complete and Project Complexity@row is High and the difference between the Project Active Date@row and the Project Complete Date@row exceeds 18 months, then set the background color of Project Complexity@row column Red. & if difference between the Project Active Date and the Project Complete Date is greater than 12 months and less than 18 months, then set the background color of Project Complexity@row column Yellow, else Green.

If Project Status@row is Complete and Project Complexity@row is Med and the difference between the Project Active Date@row and the Project Complete Date@row exceeds 12 months, then set the background color of Project Complexity@row column Red. & if difference between the Project Active Date and the Project Complete Date is greater than 6 months and less than 12 months, then set the background color of Project Complexity@row column Yellow, else Green.

 

If Project Status@row is Complete and Project Complexity@row is Low and the difference between the Project Active Date@row and the Project Complete Date@row exceeds 8 months, then set the background color of Project Complexity@row column Red. & if difference between the Project Active Date and the Project Complete Date is greater than 6 months and less than 8 months, then set the background color of Project Complexity@row column Yellow, else Green. 

Genevieve P.

Hi @BO'Neil

It sounds like the output you want is colour-coding in the colour of the cell where your Project Complexity data is stored. Instead of using a Formula then, you'll want to use Conditional Formatting in your sheet to apply the different colours.

See: Conditional Formatting

Then I would personally set up 3 Reports to show the 3 Complexity types (the three statements above) as Report Widgets in your Dashboard.

However you will need one formula before you can set up these rules. You'll need the "Scope of Work estimate in Months" column that you mentioned for the Formatting rule to evaluate as part of the criteria.

To find out the months, you can take one date and subtract it from another date in the same row. This will give you the number of days between the dates (see: Use Formulas to Perform Calculations With Dates)

Now that you have the days between your dates, you can use these three criteria ("Complete", Complexity, and timeframe) to write your Formatting Rules:

Cheers,

Genevieve