Determine the date a project enters a status

[SteelReyn]

Hi everyone,

I have a historical list of status updates for multiple projects, each with a unique Project ID. A datestamp is applied every time the status of a project is updated, even if the status does not change. For example, Project 1 might be in a status of "Healthy" and the project manager completes their weekly update (creating another row in the history log with a new datestamp), but the project is still in a "Healthy" status.

My goal is to determine how long each project has been in its most recent status. Project IDs are in Column A, Status is in Column B, and datestamps are in Column C (see attached workbook - in post #8 now - for an example). To manually determine this, I would look for the most recent date for each project, look at the Status for that row, and then go backwards until I find the row where the project entered the status this time.

Ignore the rest and see post #8 for updated info----One potential snag is that a project can transition between different statuses multiple times (e.g., Healthy, then Issues, then Healthy again), so I cannot just determine the earliest date of the most recent status. This would only tell me the first date a project entered a particular status, not the first date that the project entered a particular status this time around.

The attached workbook shows what I was able to accomplish so far (which is just determining the most recent status for each project) in Column D, and what I am trying to get for the end result in Column E. Ideally, the end result would not be dependent on sort order of the table.----

I appreciate the help - I'm in over my head!

[FDibbins]

try this and let me know if it gives you what you want?

=IF(Table1[[#This Row],[Project ID]]=A3,"",INDEX(Table1[[Status]:[Date]],MATCH(SUMPRODUCT(([Project ID]=1)*(MAX([Date]))),[Date],1),1))

[SteelReyn]

Hi FDibbins,

I tried entering the provided formula in Column E, but the result is just the word "Healthy" all the way down the column. The formula appears to only be displaying the status of the last project ID (2 in this case), since the result for Column E reads as whatever I enter in as the most recent Status for project 2 (i.e., modify cell B13 to "Issues" and Column E displays "Issues" in the entire column).

Thanks for the post, though.

[FDibbins]

i edited my formula just after i posted it, try it again and let me know?

[FDibbins]

ok wait, did i not fully undertand your requirements?

do you want to compare the "latest" update with the "next-to-latest" update?

see attachment

[SteelReyn]

Maybe I did a poor job of explaining. I am trying to find the date that each project entered its current status. This will allow me to subtract the date for that row from today's date and track how many days each project has been in its current status. The steps for me to accomplish this manually are outlined in Column G, but I can't figure out how to accomplish it formulaically.

The formula I have in Column D is just where I tried to tackle the first two steps of the process. It is not an end result to compare anything against, just where I am piecing out the individual parts of the problem.

The attached workbook shows the result of what I am trying to accomplish in Column E. From there, I can subtract the date in the rows with a value of 1 from today's date. I also included two examples of the process.

Hopefully this helps, and thank you for your work so far.

[SteelReyn]

Please let me know if something in the problem is unclear - I could still really use the help.

[SteelReyn]

I think I have made progress on this, although it might not be pretty. The attachment shows what I have been able to figure out (much thanks to DonkeyOte in another thread on lookups!), and I think all that is missing is a way to discriminate between timeframes where some projects have entered the same status multiple times. Unfortunately, I have no idea how to go about doing this.

In the attached worksheet, Column I produces the intended results, except for Project ID 1. That's because Column I technically flags the row where the project first went into its current status, not the row where the project went into its current status this time. The "1" in Column I needs to be in I7, not I4. Is there a way to get where I need to be?

[Tynus]

I have a solution, it uses VBA and requires that all ID numbers from 1 upwards are always retained; and no spaces in the list.
Note also that you can enter new info at the end of the present list and it will be incorporated with respect to date and ID order.
I looked at the problem with work sheet functions only and decided that variables were needed, so...
I am new to VBA but it looked like a good learning exercise...
I think the code is rather messy but...
Tell me if you want to have it uploaded...
Tynus