Formulas to identify current project phase, next task due on a project plan and the task due date.

Sharon B

Hi everyone,

To help us report the current position of a number of projects, we have been asked to report on the current phase and next due task. So I need to find formula's that can identify the following 3 things:-

1. What is the current phase of a project. On example below, "Defined, Scoped and Analysed" is the current phase that is in progress, and is only 25% complete. I need to return the phase name as the value.
2. What the next incomplete task is from a project plan, and return the task description. On example below, the next due task is the one highlighted in yellow and I need to show the task description as the returned value.
3. What the Actual End date of that task is. I separately need to identify the actual end date for the next incomplete task that is highlighted in yellow.

Please could anyone advise how we could do this and show on a report? Happy to pull the formula onto a sheet and then do a report from that sheet. I simply cannot get my head round how to do this at all. I have read many articles showing similar requests but not the exact same. The formulas I have seen have been complex, and I am finding it hard to break down and understand what each component part of the formula is aiming to achieve so that I can apply the relevant logic to my scenarios. So any help in explaining the logic as well as the formula would be very, very much appreciated!

Thanks in anticipation,

Sharon

Genevieve P.

Hi @Sharon B

1) Current Phase

You nicely have a structure where the Current Phase has a blank task name, which will help us identify which one is In Progress! We will just need to exclude any rows that have content in the Task Name column, or the "Framework Offer" row, and then pull the Task Description for the row that's "In Progress".

To do this, we'll use a Join Collect (in case you ever have two Phases in progress at once... if you only have one then it will only show one).

The structure of a Join Collect is as follows:

=JOIN(COLLECT({Column to Return}, {Column 1}, "Criteria 1", {Column 2}, "Criteria 2".... etc

I believe we can create these three values in the Sheet Summary section, so instead of {cross sheet references} we can call out your actual columns in the current sheet.

So in your case, try this:

=JOIN(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <> "Framework Offer", Status:Status, "In Progress"), " , ")

2) Next Incomplete Task

This gets a little trickier as you want to deal with Dates, too. We can still use the criteria that the Task Name now needs to have content (to find the child row, this time), then look for either "In Progress" or "Not Started", but we also need to find the earliest or MIN date.

It will look a little complex just because we need to reference the same criteria two times - once for the MIN formula and then again for the INDEX function.

Structure:

=INDEX(COLLECT({Column to Return}, {Column 1}, "Criteria 1", {Column 2}, "Criteria 2", {Date Column}, MIN(COLLECT({Date Column}, {Column 1}, "Criteria 1", {Column 2}, "Criteria 2"))), 1)

Try this:

=INDEX(COLLECT([Task Description]:[Task Description], Status:Status, <>"Complete", [Task Name]:[Task Name], <>"", [Actual Start Date]:[Actual Start Date], MIN(COLLECT([Actual Start Date]:[Actual Start Date], Status:Status, <>"Complete", [Task Name]:[Task Name], <>""))), 1)

3) Actual End Date

I believe your Task Descriptions are unique, is that correct? If so, this should be the easiest one!

You already have the Task Description from the previous formula, so now we just need to find that and return the Actual End Date.

Structure:

=INDEX({Date to Return}, MATCH("Value", {Value to Match}, 0))

Try this:

=INDEX([Actual End Date]:[Actual End Date], MATCH([Next Task]#, [Task Description]:[Task Description]))

NOTE: My Value is [Next Task]# because I'm referencing a Sheet Summary field in my sheet as the Value to Match:

Let me know if this has worked for you, and if each of your questions have a correct solution!

Cheers,

Genevieve

Sharon B

Hi @Genevieve P.

Thanks so much for your help with this. it has worked to bring back the correct results for all 3 scenarios for the test plan I shared. Note rather than pull the formula into sheet summary, I have pulled it into a new column on the plan - the reason being we have already got reports set up based on data from the columns and we want to add to those, rather than have to start again or provide a separate report. Also I tweeked the formula for 2 to work on the Actual End date as it was actually pulling back the wrong date otherwise - it was pulling back the date for the task on row 10 (below Stakeholder Engaged parent) as the start date of that task was earlier than the first incomplete task above. So all was working well when I made that adjustment

I have then tried to apply the same formulae to a live project but the date hasn't worked for me. So you can see have added "current stage", "next task due" and "next task due date" columns and used the formulas you gave me for 1 and 2. But for formula 3 it is returning the wrong date (the latest end date on the whole plan) or now saying #NO MATCH. The difference between what you suggested is instead of using Next Task#, I am pointing the formula to look for the task shown in the "next task due" field. It is now saying #No Match.

The formula in the "next task due" field is =INDEX(COLLECT([Task Description]:[Task Description], Status:Status, <>"Complete", [Task Name]:[Task Name], <>"", [Actual End Date]:[Actual End Date], MIN(COLLECT([Actual End Date]:[Actual End Date], Status:Status, <>"Complete", [Task Name]:[Task Name], <>""))), 1)

The formula in the "next task due date" is =INDEX([Actual End Date]:[Actual End Date], MATCH([Next Task Due]@row, [Task Description]:[Task Description]))

Any idea why this doesn't work for this specific plan, but did work for the plan below using same formulae?

Thanks again so much for your help.

Genevieve P.

Hi @Sharon B

The NOMATCH means that your formula is correct, but it can't seem to find that value in the Task Description column... can we try adding the 0 in the MATCH portion of the formula?

=INDEX([Actual End Date]:[Actual End Date], MATCH([Next Task Due]@row, [Task Description]:[Task Description], 0))

This tells the formula the Sort Order of your sheet, noting that the sheet is not sorted so it has to look through each cell. It may have been trying to search alphabetically, which is why it couldn't find a match.

Let me know if this was the issue! If it was, I would recommend updating your other sheet to have the 0 as well. 🙂

Cheers,

Genevieve

Sharon B

Hi,

Yes that works thanks. I'm nearly there but have stumbled upon another small challenge when I have come to another project with a scenario that I hadn't considered and only came across when applying to the logic to the various plans. We actually have 4 possible phase options which may be "In progress", "Not started", "Cancelled", "Not Applicable" or "Complete". Where they say cancelled or not applicable, we would remove actual start and end dates from those tasks.

We should be in a situation where all current project phases are "In Progress", but in reality a user may not have updated the plan. So I have come across scenarios where the next phase due to start has a status of "Not Started" as opposed to "In Progress".

So see example screenshot below, the "Evaluation" phase is completed but the "Award Signed off" phase has not yet been started. So I have changed formula to the following:

=JOIN(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <>[Project Name (Trust Name/Product Category)]@row, Status:Status, <>"Complete"), " , ").

It seems to be working, bringing back the correct current phase of Award Signed off.

I just wanted to double check before I apply this revision to all the plans I've updated so far - would this always then bring back the correct phase i.e. the project phase that is currently "In progress" or "Not Started", or could you foresee any scenario this may not work?

Thanks again - you have been amazingly helpful :).

Kind regards,

Sharon

Sharon B

Hi, Darn, I have realised that using the formula above with <> Complete at the end doesn't work - it brings back any phase that is "In progress" or "Not Started" - So will bring back multiple as opposed to which is just the current phase we are due to start or currently in.

So I have tried:

=JOIN(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <>[Project Name (Trust Name/Product Category)]@row, Status:Status, "In Progress", Status:Status, "Not Started"), " , ").

That doesn't work - just brings back a blank result as nothing found. Maybe we need to add in a logical bit that also brings back the earliest start or end date? Sorry to complicate things, but any help greatly appreciated.

Genevieve P.

Hi @Sharon B

Thanks for clarifying how your sheet is set up! In this case, it will be easiest if we identify the 3 status types that we don't want to bring back, because then we can use a sort of AND statement inside of the COLLECT function.

So instead of just saying

Status:Status, <> "Complete"

We can say three times what we don't want the status to be:

Status:Status, <> "Complete", Status:Status, <> "Cancelled", Status:Status, <> "Not Applicable"

You'll need to replace that anywhere you have the single version. For example:

=INDEX(COLLECT([Task Description]:[Task Description], Status:Status, <> "Complete", Status:Status, <> "Cancelled", Status:Status, <> "Not Applicable", [Task Name]:[Task Name], <>"", [Actual End Date]:[Actual End Date], MIN(COLLECT([Actual End Date]:[Actual End Date], Status:Status, <> "Complete", Status:Status, <> "Cancelled", Status:Status, <> "Not Applicable", [Task Name]:[Task Name], <>""))), 1)

Does that make sense? Let me know if this now accounts for all of your criteria!

Cheers,

Genevieve

Sharon B

Hi Again,

Apologies the updated formula you gave me is for step 2 - to bring back the task description formula you gave me, whereas I am looking at the first formula to bring back the current phase of the project i.e. =JOIN(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <> "Framework Offer", Status:Status, "In Progress"), " , ").

Based on the logic you suggested I tried the formula below, but it is still bringing back more than one stage i.e. it is bringing back all the incomplete phases as opposed to just the next phase due i.e. Award Signed Off - see screen shot of field called Current Stage below.

I tried this : =JOIN(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <>[Project Name (Trust Name/Product Category)]@row, Status:Status, <>"Complete", Status:Status, <>"Cancelled", Status:Status, <>"Not Applicable"), " , ")

Sorry we never make things simple do we!

Thanks,

Sharon

Genevieve P.

Hi @Sharon B

My apologies! Yes, the JOIN will bring together all of the Phases that match your criteria. If you only want to see the first one in the list, swap the JOIN to be an INDEX and have the Row Reference be 1 (to indicate just the first row brought back).

=INDEX(COLLECT([Task Description]:[Task Description], [Task Name]:[Task Name], "", [Task Description]:[Task Description], <>[Project Name (Trust Name/Product Category)]@row, Status:Status, <>"Complete", Status:Status, <>"Cancelled", Status:Status, <>"Not Applicable"), 1)

Sharon B

Hurrah - it works -and I fully understand the formula workings now you explained what the 1 reference means as well, so I have been able to use adapt it to also bring back the current task owner as well. I think I totally get it and understand how the component formula parts work together now so I can;t thank you enough for your help. I am a very happy lady :).

Genevieve P.

Oh I'm so glad! I'm happy I could help. 🙂